Populate form label with Worksheet's Cell Content
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Populate form label with Worksheet's Cell Content
Hi Anyone,
I'm trying to populate a userform in the following way.
I want to know whether it is possible to populate a "label" in the userform; from the value that I write in a cell in a worksheet.
Say for an example if I write Mango in cell A1 and when I click the macro button in the sheet and open the userform I want the Mango that I wrote in the cell A1 to appear in the label that I have created in the userform.
I hope I have made my question clear.
Any help would be kindly appreciated.
I'm trying to populate a userform in the following way.
I want to know whether it is possible to populate a "label" in the userform; from the value that I write in a cell in a worksheet.
Say for an example if I write Mango in cell A1 and when I click the macro button in the sheet and open the userform I want the Mango that I wrote in the cell A1 to appear in the label that I have created in the userform.
I hope I have made my question clear.
Any help would be kindly appreciated.
Last edited by HansV on 08 Apr 2010, 21:01, edited 1 time in total.
Reason: to correct typo in subject
Reason: to correct typo in subject
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate form label with Worksheet's Cell Content
You can do this in the UserForm_Initialize event procedure in the code module of the userform:
See the attached demo workbook.
Code: Select all
Private Sub UserForm_Initialize()
Me.Label1.Caption = Range("A1")
End Sub
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
Thanks for the quick reply & detail explanation, Hans. That was really helpful & I sis like the sample file.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
How could I modify the code if I want to populate the userform's textbox with the name "txtPatientID" with the text that I write in the cell "D8" of the sheet "order"
I did try using the following code
But it does not seem to work. What have I done wrong in here?
I did try using the following code
Code: Select all
Private Sub UserForm_Initialize()
Me.txtCustomerID.Caption = Range("D8")
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate form label with Worksheet's Cell Content
Caption is a property of a label, not of a text box. Try using Value instead of Caption.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
Thankyou Hans. It worked fine & I'm happy with that.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
I'm using the following code so the text box gets populated with the text in the referenced cell. But when the form is open and when I changed the text in the cell C36. that value does not seem to show up in text box txtTotalWords.
The text box shows only the value that was in the cell when the user form was loaded.
How may I do so?
The text box shows only the value that was in the cell when the user form was loaded.
How may I do so?
Code: Select all
Private Sub UserForm_Initialize()
Me.txtTotalWords.Value = Sheets("Previous").Range("C36").Text
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate form label with Worksheet's Cell Content
How do you change the text in cell C36 when the form is open?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
The text in cell C36 gets changed through the code posted in Post=12544 after the following code fills the cell D3. Meaning When I change the serial number form the text box the text in the text box gets copied to the cell C36 and the code embedded in the worksheet fills the rest.
Note: Instead of
The current code uses
Code: Select all
Private Sub txtSerialNo_change()
Sheets("Previous").Range("D3") = Me.txtSerialNo.Text
End Sub
Code: Select all
Range("A16:A25").ClearContents
Range("I16:I25").ClearContents
Code: Select all
Range("A16:A35").ClearContents
Range("I16:I35").ClearContents
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate form label with Worksheet's Cell Content
Add code to txtSerialNo_change to update the value of txtTotalWords.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
Thank You Hans. Now I can get what I had asked after adding the code to txtSerialNo_change in order to update the value of txtTotalWords.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate form label with Worksheet's Cell Content
I’m trying to embed the following code to my userform at Post=12986 to save the active workbook and then unload the form with the help of the close button in the excel user form.
The code saves the workbook but does not unload the user form.
What may be the reason for this?
The code saves the workbook but does not unload the user form.
What may be the reason for this?
Code: Select all
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
ThisWorkbook.Save
Unload Me
End If
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 7220
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Populate form label with Worksheet's Cell Content
Try debugging it yourself:adam wrote:What may be the reason for this?
Add a Pause (break point) on the line beginning "If CloseMode =" then step through when the macro is called.
If the editor steps from If CloseMode... to End If then CloseMode <> vbFormControlMenu
Leif
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate form label with Worksheet's Cell Content
Here's another clue for you all:
There shouldn't be a need to unload the userform in the QueryClose event. If you want the userform to be closed, you don't have to do anything. If you want to prevent the userform being closed, use Cancel = True.
There shouldn't be a need to unload the userform in the QueryClose event. If you want the userform to be closed, you don't have to do anything. If you want to prevent the userform being closed, use Cancel = True.
Best wishes,
Hans
Hans