userform txt box contents

[Guest]

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

 

[Harald Staff]

Hi Nigel

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

HTH. best wishes Harald

 

[Guest]

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]

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

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

HTH. Best wishes Hatald

 

[Guest]

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]

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

HTH. Best wishes Harald

 

[Guest]

Hi,

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

Regards,

Nigel

 

[Harald Staff]

Ok. hstf at hotmail dot com

Harald

 

[Harald Staff]

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

 

[Guest]

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]

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

 

[Guest]

Thank you for your help and guidance Harald.

Nigel