Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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 |
#5
![]() |
|||
|
|||
![]()
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 |
#6
![]() |
|||
|
|||
![]()
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 |
#7
![]() |
|||
|
|||
![]()
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 |
#8
![]() |
|||
|
|||
![]()
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. |
#9
![]() |
|||
|
|||
![]()
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. |
#10
![]() |
|||
|
|||
![]()
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. |
#11
![]() |
|||
|
|||
![]()
"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 |
#12
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Cell Content from UserForm Not Retained | Excel Discussion (Misc queries) | |||
Can I use the contents of a cell to satisfy the result_vector arg. | Excel Worksheet Functions | |||
How can I run a macro in the background whilst a UserForm is visib | Excel Discussion (Misc queries) | |||
Cell contents vs. Formula contents | Excel Discussion (Misc queries) |