Using Command Buttons and UserForm Lists in Conjunction

[curleyy]

Hi all,

I have a macro running in which I am using a UserForm with a dropdown list. The list has three options (pulled from sheet called "Daily").
The operator is required to select one from the list and then continue or cancel.

The command button to continue is required to copy the option selected by the operator and copy it to cell "AZ1" on sheet "Daily".
I cannot get the code for the command button to work;
Sub cmdAdd_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("AZ1").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
End Sub


Any suggestions on the code or otherwise? I'm open to anything at this stage!

Thanks in advance!!

 

[Norie]

Why are you referring to a textbox and not a combobox?

Also, why are you starting in row 1 to find the last row? Doing that you'll always end up in row 1.

Try starting from the bottom.
[cod]e
Set LastRow = Sheet1.Range("AZ" & Rows.Count).End(xlUp)
[/code]

 

[moheganburner]

Why are you pasting the selected option in Range AZ1? Is it because that is how you are transferring it back to the Macro? If so, I would declare a public variable in your macro, such as:

Public optSelection

and then set it equal to the selected option upon cmdAdd_Click. Once something is declared publicly, it can be referred to anywhere in workbook.

Assuming you are using a listbox on your userform, your cmdAdd_Click code could look something like:

Private Sub CmdAdd_Click()
If ListBox1.Value <> "" Then optSelection = ListBox1.Value
UserForm1.Hide
End Sub

Now your macro will have the selected option saved as optSelection.

 

[Norie]

Why are you referring to a textbox and not a combobox?

Also, why are you starting in row 1 to find the last row? Doing that you'll always end up in row 1.

Try starting from the bottom.

Set LastRow = Sheet1.Range("AZ" & Rows.Count).End(xlUp)

 

[curleyy]

This was exactly what I was looking for! Thank you very much