Initializing User Forms
 

You don't need to go through two arrays to do this:

Private Sub fmPickStore_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

If you only want to load 1 column

Private Sub fmPickStore_Initialize()
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("B3:B22").Value
End Sub

--
Regards,
Tom Ogilvy


"Marcotte A" wrote in message
...
I am having trouble initializing my user forms. I have a drop down list
that I want populated from a range on my spreadsheet. When that spreadsheet
is not active, the combobox doesn't get initialized. This is the initialize
code. I thought the 'ThisWorkbook' part would take care of it, but it
doesn't seem to. What am I doing wrong?

Private Sub fmPickStore_Initialize()
Dim varStoreList As Variant
Dim arrStoreList(20, 2) As Variant
Dim i As Integer
varStoreList = ThisWorkbook.Worksheets("Chart Data").Range("b3:c22")
For i = 1 To 20
arrStoreList(i, 1) = varStoreList(i)
Next i
cbStoreList.List = arrStoreList
End Sub

Initializing User Forms
 

"Tom Ogilvy" wrote:

You don't need to go through two arrays to do this:

Private Sub fmPickStore_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

Thanks Tom. I knew there was a more efficient way to do this. However, I am still not seeing the list when another workbook is active. When the workbook containing the userform is active it works fine, but if I go to another workbook, then into the VBE and run my main() sub (which loads the userform) I get an empty dropdown list. Here is the code:

'In Module2
Public CurrentStore As Integer
Public InputType As String
Public CurrentDate As Date
Sub main()
CurrentDate = Date
MainMenu.Show
End Sub

'Partial code for MainMenu form
Private Sub cmProduction_Click()
InputType = "ProdSpoil"
Me.Hide
fmpickstore.Show
End Sub

'Partial code for fmPickStore
Private Sub fmPickStore_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

Any ideas?

Initializing User Forms
 

The initialize event always has the name

Private Sub Userform_Initialize()

so your sub is never run

change to

Private Sub Userform_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

It is always best to use the dropdowns at the top of the module to enter
events - leads to fewer mistakes such as this.

--
Regards,
Tom Ogilvy


"Marcotte A" wrote in message
...
"Tom Ogilvy" wrote:

You don't need to go through two arrays to do this:

Private Sub fmPickStore_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

Thanks Tom. I knew there was a more efficient way to do this. However, I
am still not seeing the list when another workbook is active. When the
workbook containing the userform is active it works fine, but if I go to
another workbook, then into the VBE and run my main() sub (which loads the
userform) I get an empty dropdown list. Here is the code:

'In Module2
Public CurrentStore As Integer
Public InputType As String
Public CurrentDate As Date
Sub main()
CurrentDate = Date
MainMenu.Show
End Sub

'Partial code for MainMenu form
Private Sub cmProduction_Click()
InputType = "ProdSpoil"
Me.Hide
fmpickstore.Show
End Sub

'Partial code for fmPickStore
Private Sub fmPickStore_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

Any ideas?

Initializing User Forms
 

"Tom Ogilvy" wrote:

The initialize event always has the name

Private Sub Userform_Initialize()

so your sub is never run

change to

Private Sub Userform_Initialize()
cbStoreList.ColumnCount = 2
cbStoreList.List = _
ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value
End Sub

It is always best to use the dropdowns at the top of the module to enter
events - leads to fewer mistakes such as this.

--
Regards,
Tom Ogilvy

I changed the name of the initialize sub as suggested and now get Runtime Error #70 'Permission Denied'. I switched it back to fmpickstore_initialize and it works fine. I added 'ThisWorkbook.Activate' to the initialize event for my main menu form, so its working all the time now.
It's odd though, because each other userform initialize event is Sub UserForm_Initialize (), but when I change Sub fmPickStore_Initialize () to Sub UserForm_Initialize () I get that error. It makes no sense.