10101
10101

Reputation: 2412

Chosen result from Worksheet cell to Userform Combobox

I have Userform that have combobox. Combobox picks range from Workbook and inputs picked up result to cell C79 by this VBA:

Private Sub ComboBox1_Change()
ThisWorkbook.Worksheets("Other Data").Range("C79").Value = Me.ComboBox1.Value
End Sub

The problem is when I open Userform for the second time I can't see picked up result in combobox so I have to pick it up again. How to link cell C79 to Private Sub UserForm_Initialize() so that when I open UserForm, value from C79 will be visible in Combobox1?

I have tried:

Private Sub UserForm_Initialize()
    ComboBox1.List = ThisWorkbook.Sheets("Other Data").Range("A79:A81").Value ' This one picks the range
    'ThisWorkbook.Sheets("Other Data").Range("C79").Value = ReviewForm.ComboBox1.Value
End Sub

Upvotes: 1

Views: 35

Answers (1)

Tim Stack
Tim Stack

Reputation: 3248

To populate a ComboBox control in a UserForm, use the following

Private Sub UserForm_Initialize()
    Me.ComboBox1.Value = ThisWorkbook.Sheets("Other Data").Range("C97").Value
End Sub

Alternatively, you could update this value each time the UF is activated:

Private Sub UserForm_Activate()
    Me.ComboBox1.Value = ThisWorkbook.Sheets("Other Data").Range("C97").Value
End Sub

Or, you could update the UF's combobox every time the cell value changes. This is not logical however, since you update the cell with the UF. It would activate itself.

Upvotes: 3

Related Questions