Userform Initialize & Command Button, Excel 2000 & 2003

[jfcby]

Hello,

I have a userform with a combo box and a commandbutton. The combobox
uses the .additem, currently it has 11 items but will be added to. My
UserForm_Initialize is below:

Private Sub UserForm_Initialize()
Dim varData(10) As Variant
varData(0) = ""
varData(1) = "Search"
varData(2) = "Find"
varData(3) = "GoTo"
varData(4) = "Compare Data"
varData(5) = "Delete Characters"
varData(6) = "Number Cells 1-25"
varData(7) = "Number Sells Value 001"
varData(8) = "Find Blank Cell"
varData(9) = "Fill Selection Copy"
varData(10) = "Fill Selection Series"
With ComboBox1
.AddItem varData(0)
.AddItem varData(1)
.AddItem varData(2)
.AddItem varData(3)
.AddItem varData(4)
.AddItem varData(5)
.AddItem varData(6)
.AddItem varData(7)
.AddItem varData(8)
.AddItem varData(9)
.AddItem varData(10)
End With
End Sub

My commandbutton is below:

Private Sub CommandButton3_Click()
cb = ComboBox1
If cb = "" Then
'Do Nothing
ElseIf cb = "Search" Then
Call UserFormFIND
ElseIf cb = "Find" Then
Call FindDialogBoxOpen
ElseIf cb = "GoTo" Then
Call GoToDialogBoxOpen
ElseIf cb = "Compare Data" Then
Call CompareData
ElseIf cb = "Delete Charcters" Then
Call DeleteCharcters
ElseIf cb = "Number Cells 1-25" Then
Call NumberCells
ElseIf cb = "Number Cels Value 001" Then
Call NumberCellsValue
ElseIf cb = "Find Blank Cells" Then
Call FindBlankCell
ElseIf cb = "Fill Selection Copy" Then
Call Selection.FillDown
ElseIf cb = "Fill Selection Series" Then
Call FillSelection_Series
End If
End Sub


All of my elseif has the same name as my UserForm_Initialize. How can
I use the UserForm_Initialize varData array in my command button so
that I do not have to type the list twice?

Thank you for your help,
jfcby

 

[merjet]

Put the declaration -- Dim varData(10) As Variant -- before any
procedures. That makes it available to all procedures in the module.

Hth,
Merjet

 

[Dave Peterson]

First, you'll want to watch your spelling--and case is important in stuff like
this, too.
"Delete Characters" would never match: "Delete Charcters"

But instead of checking the names, maybe just using the index:

Option Explicit
Dim varMacs As Variant
Private Sub UserForm_Initialize()
Dim varData As Variant


varData = Array("", "Search", "Find", "GoTo", _
"Compare Data", _
"Delete Characters", "Number Cells 1-25", _
"Number Cells Value 001", "Find Blank Cell", _
"Fill Selection Copy", "Fill Selection Series")

varMacs = Array("", "UserFormFind", "FindDialogBoxOpen", _
"GoToDialogBoxOpen", _
"CompareData", _
"DeleteCharacters", "NumberCells", _
"NumberCellsValue", "FindBlankcell", _
"SelectionFillDown", "FillSelectionSeries")

If UBound(varData) <> UBound(varMacs) Then
MsgBox "Design error!"
Exit Sub
End If


With ComboBox1
.List = varData
End With
End Sub

Private Sub CommandButton1_Click()
If Me.ComboBox1.ListIndex < 1 Then
Beep
Exit Sub
Else
'Application.Run "'" & ThisWorkbook.Name & "'!" & varMacs(.ListIndex)
MsgBox "'" & ThisWorkbook.Name & "'!" & varMacs(Me.ComboBox1.ListIndex)
End If
End Sub

You'll want to make sure that the macro names actually match the names you used
in that varMacs assignment--no typos <vbg>!

And I used msgbox instead of actually creating those routines.

 

[Tom Ogilvy]

I would check the spelling between you array values and your if statements.

If you are going to call different procedures you could use the listindex
property.

select Case combobox1.listindex
Case -1
' do nothing
Case 0
UserFormFIND
Case 1
FindDialogBoxOpen
Case 2
GoToDialogBoxOpen

. . .
End Select

 

[jfcby]

I would check the spelling between you array values and your if statements.

If you are going to call different procedures you could use the listindex
property.

select Case combobox1.listindex
Case -1
' do nothing
Case 0
UserFormFIND
Case 1
FindDialogBoxOpen
Case 2
GoToDialogBoxOpen

. . .
End Select

Hello All responders,

Thank you for your help. Problem was solved with Tom's select case
response.

jfcby