userform txt box contents
 

i have a userform. on the form is a text box called txtid. i need it to show
content of a formula as follows: =counta(A1:A1000)+1. can anyone help me?
i can email the workbook if needed.

regards,

nigel

Hi Nigel

Txtid.Text = Range("A1").Formula

HTH. best wishes Harald

"Nigel" skrev i melding
...
i have a userform. on the form is a text box called txtid. i need it to
show
content of a formula as follows: =counta(A1:A1000)+1. can anyone help me?
i can email the workbook if needed.

regards,

nigel

Hi,

I cant seem to get it to work. here is the complete code for the userform:

Part Taken from an existing workbook :)


Private Sub cmdAdd1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Contacts")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Contact
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a Contact"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtId.Value
ws.Cells(iRow, 2).Value = Me.txtPart.Value
ws.Cells(iRow, 3).Value = Me.txtLoc.Value
ws.Cells(iRow, 4).Value = Me.txtDate.Value
ws.Cells(iRow, 5).Value = Me.txtfax.Value
ws.Cells(iRow, 6).Value = Me.txtQty.Value
ws.Cells(iRow, 7).Value = Me.txtemail.Value

'clear the data
Me.TxtId.Value = ""
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtfax.Value = ""
Me.txtQty.Value = ""
Me.txtemail.Value = ""
Me.txtPart.SetFocus

End Sub

Private Sub cmdClose1_Click()
Unload Me
End Sub

Private Sub txtDate_Change()

End Sub

Where or what do i change to show the data required? on the contacts sheet,
A1 has a formula of: =COUNTA(A1:A1000)+1 which basically counts the amount of
contacts to date then adds 1 for the next contact. i need this to show in the
txtid box.


Regards,

Nigel




"Harald Staff" wrote:

Hi Nigel

Txtid.Text = Range("A1").Formula

HTH. best wishes Harald

"Nigel" skrev i melding
...
i have a userform. on the form is a text box called txtid. i need it to
show
content of a formula as follows: =counta(A1:A1000)+1. can anyone help me?
i can email the workbook if needed.

regards,

nigel

I thought you wanted to see the formula, not its result. Sorry. He

Me.TxtId.Value = Range("A1").Value

HTH. Best wishes Hatald

"Nigel" skrev i melding
...
Hi,

I cant seem to get it to work. here is the complete code for the userform:

Part Taken from an existing workbook :)


Private Sub cmdAdd1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Contacts")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Contact
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a Contact"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtId.Value
ws.Cells(iRow, 2).Value = Me.txtPart.Value
ws.Cells(iRow, 3).Value = Me.txtLoc.Value
ws.Cells(iRow, 4).Value = Me.txtDate.Value
ws.Cells(iRow, 5).Value = Me.txtfax.Value
ws.Cells(iRow, 6).Value = Me.txtQty.Value
ws.Cells(iRow, 7).Value = Me.txtemail.Value

'clear the data
Me.TxtId.Value = ""
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtfax.Value = ""
Me.txtQty.Value = ""
Me.txtemail.Value = ""
Me.txtPart.SetFocus

End Sub

Private Sub cmdClose1_Click()
Unload Me
End Sub

Private Sub txtDate_Change()

End Sub

Where or what do i change to show the data required? on the contacts
sheet,
A1 has a formula of: =COUNTA(A1:A1000)+1 which basically counts the amount
of
contacts to date then adds 1 for the next contact. i need this to show in
the
txtid box.


Regards,

Nigel




"Harald Staff" wrote:

Hi Nigel

Txtid.Text = Range("A1").Formula

HTH. best wishes Harald

"Nigel" skrev i melding
...
i have a userform. on the form is a text box called txtid. i need it
to
show
content of a formula as follows: =counta(A1:A1000)+1. can anyone help
me?
i can email the workbook if needed.

regards,

nigel

Hi,

Stil cannot get it to show anything. Where do i put or change the
Me.TxtId.Value = Range("A1").Value
i have already put this in various places but it shows nothing.

Regards,

Nigel


"Harald Staff" wrote:

I thought you wanted to see the formula, not its result. Sorry. He

Me.TxtId.Value = Range("A1").Value

HTH. Best wishes Hatald

"Nigel" skrev i melding
...
Hi,

I cant seem to get it to work. here is the complete code for the userform:

Part Taken from an existing workbook :)


Private Sub cmdAdd1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Contacts")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Contact
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a Contact"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TxtId.Value
ws.Cells(iRow, 2).Value = Me.txtPart.Value
ws.Cells(iRow, 3).Value = Me.txtLoc.Value
ws.Cells(iRow, 4).Value = Me.txtDate.Value
ws.Cells(iRow, 5).Value = Me.txtfax.Value
ws.Cells(iRow, 6).Value = Me.txtQty.Value
ws.Cells(iRow, 7).Value = Me.txtemail.Value

'clear the data
Me.TxtId.Value = ""
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtfax.Value = ""
Me.txtQty.Value = ""
Me.txtemail.Value = ""
Me.txtPart.SetFocus

End Sub

Private Sub cmdClose1_Click()
Unload Me
End Sub

Private Sub txtDate_Change()

End Sub

Where or what do i change to show the data required? on the contacts
sheet,
A1 has a formula of: =COUNTA(A1:A1000)+1 which basically counts the amount
of
contacts to date then adds 1 for the next contact. i need this to show in
the
txtid box.


Regards,

Nigel




"Harald Staff" wrote:

Hi Nigel

Txtid.Text = Range("A1").Formula

HTH. best wishes Harald

"Nigel" skrev i melding
...
i have a userform. on the form is a text box called txtid. i need it
to
show
content of a formula as follows: =counta(A1:A1000)+1. can anyone help
me?
i can email the workbook if needed.

regards,

nigel

Wherever it now says
Me.TxtId.Value = ""
or anywhere else beginning with
Me.TxtId.Value =

HTH. Best wishes Harald


"Nigel" skrev i melding
...
Hi,

Stil cannot get it to show anything. Where do i put or change the
Me.TxtId.Value = Range("A1").Value
i have already put this in various places but it shows nothing.

Regards,

Nigel

Hi,

Can i email the workbook for you to look at? nothing seems to show in the
desired textbox.

Regards,

Nigel


"Harald Staff" wrote:

Wherever it now says
Me.TxtId.Value = ""
or anywhere else beginning with
Me.TxtId.Value =

HTH. Best wishes Harald


"Nigel" skrev i melding
...
Hi,

Stil cannot get it to show anything. Where do i put or change the
Me.TxtId.Value = Range("A1").Value
i have already put this in various places but it shows nothing.

Regards,

Nigel

Ok. hstf at hotmail dot com

Harald

"Nigel" skrev i melding
...
Hi,

Can i email the workbook for you to look at? nothing seems to show in the
desired textbox.

Hi Nigel

Ok, read the file. Now consider
- Is "A1" really the cell where your formula is ? (No, it's in E2 ... ) You
didn't tell, so it's sort of a convention to say it's in A1 and then you
replace A1 with the real address;
- WHEN is this going to happen ? You are putting this in the Click code of
the form's cmdAdd1 button. But that's a little late ?

Apologies for not just posting a paste&go4free solution. I want you to think
this through ;-)

HTH. Best wishes Harald


"Harald Staff" skrev i melding
...
Ok. hstf at hotmail dot com

Harald

"Nigel" skrev i melding
...
Hi,

Can i email the workbook for you to look at? nothing seems to show in
the
desired textbox.

Harald,
I have successfully completed my task. i carried out the following:
1. moved away from the cmdadd1 routine
2. Looked at the initial button being pressed
3. Added the TxtId.Text = Range("Contacts!A1")
4. Then entered userform show
5. Changed values inside form to coincide with instruction

it now works just as i want it to. I am not very good at this as i have only
been doing this for about 2 months. I am from a deisel programming background
in autocad. I am not sure where your remarks were leading in regard to
"You didn't tell, so it's sort of a convention to say it's in A1 and then you
replace A1 with the real address;
- WHEN is this going to happen?" but i'm sure it is in good faith :) i have
been struggling with this type of work that i have been i suppose, pushed
into by employment requirements!! Ibthank you very much for all of your help
in his matter and i'm sure, will be in contact again.

Kindest Regards,

Nigel


"Harald Staff" wrote:

Hi Nigel

Ok, read the file. Now consider
- Is "A1" really the cell where your formula is ? (No, it's in E2 ... ) You
didn't tell, so it's sort of a convention to say it's in A1 and then you
replace A1 with the real address;
- WHEN is this going to happen ? You are putting this in the Click code of
the form's cmdAdd1 button. But that's a little late ?

Apologies for not just posting a paste&go4free solution. I want you to think
this through ;-)

HTH. Best wishes Harald


"Harald Staff" skrev i melding
...
Ok. hstf at hotmail dot com

Harald

"Nigel" skrev i melding
...
Hi,

Can i email the workbook for you to look at? nothing seems to show in
the
desired textbox.

"Nigel" skrev i melding
...

it now works just as i want it to.

Glad to hear that Nigel.

I am not sure where your remarks were leading in regard to
"You didn't tell, so it's sort of a convention to say it's in A1 and then
you
replace A1 with the real address;
- WHEN is this going to happen?" but i'm sure it is in good faith :)

Yes, I was trying to help you spot the problems, rather than simply doing it
for you. You managed anyway ;-)

Best wishes Harald

Thank you for your help and guidance Harald.

Nigel

"Harald Staff" wrote:

"Nigel" skrev i melding
...

it now works just as i want it to.

Glad to hear that Nigel.

I am not sure where your remarks were leading in regard to
"You didn't tell, so it's sort of a convention to say it's in A1 and then
you
replace A1 with the real address;
- WHEN is this going to happen?" but i'm sure it is in good faith :)

Yes, I was trying to help you spot the problems, rather than simply doing it
for you. You managed anyway ;-)

Best wishes Harald