Populate form label with Worksheet's Cell Content

[adam]

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.

[HansV]

You can do this in the UserForm_Initialize event procedure in the code module of the userform:

Code: Select all

Private Sub UserForm_Initialize()
  Me.Label1.Caption = Range("A1")
End Sub

See the attached demo workbook.

FormDemo.xlsm

[adam]

Thanks for the quick reply & detail explanation, Hans. That was really helpful & I sis like the sample file.

[adam]

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

Code: Select all

Private Sub UserForm_Initialize()
  Me.txtCustomerID.Caption = Range("D8")
End Sub

But it does not seem to work. What have I done wrong in here?

[HansV]

Caption is a property of a label, not of a text box. Try using Value instead of Caption.

[adam]

Thankyou Hans. It worked fine & I'm happy with that.

[adam]

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?

Code: Select all

Private Sub UserForm_Initialize()
 Me.txtTotalWords.Value = Sheets("Previous").Range("C36").Text
End Sub

[HansV]

How do you change the text in cell C36 when the form is open?

[adam]

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.

Code: Select all

Private Sub txtSerialNo_change()
Sheets("Previous").Range("D3") = Me.txtSerialNo.Text
End Sub

Note: Instead of

Code: Select all

    Range("A16:A25").ClearContents
    Range("I16:I25").ClearContents

The current code uses

Code: Select all

    Range("A16:A35").ClearContents
    Range("I16:I35").ClearContents

[HansV]

Add code to txtSerialNo_change to update the value of txtTotalWords.

[adam]

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.

[adam]

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?

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

[Leif]

What may be the reason for this?

Try debugging it yourself:

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

[HansV]

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.