Qn: Define a variable in Initialize for Userform

[Michael Vaughan]

Hello Everyone,

How do I define a variable in order to use in the initialize part of a
userform. I am using John's Walkenbach's Help File example and I wanted the
program to default to a certain Help File based on info provided in 1 cell.
I have 4 airplanes, the airplane is selected in Cells(30,1). Based on which
airplane I select gives the topic number to start off with. This is what I
have below, but when I run it, I get errors, it doesn't like Airplane.Text,
because I don't think it is defined anywhere??. How can I read what
airplane is in the cell to select the correct topic??
(This code does NOT work)

Airplane.Text = Cells(30, 1)
If Airplane = "N2AJ" Then CurrentTopic = 1
If Airplane = "N9421D" Then CurrentTopic = 2
If Airplane = "N74NM" Then CurrentTopic = 3
If Airplane = "N146K" Then CurrentTopic = 4 Else CurrentTopic = 1

 

[Norman Jones]

Hi Michael,

Try:

Sub Tester()
Dim Airplane As String
Dim CurrentTopic As Long
Airplane = Cells(1, 1).Value

Select Case Airplane
Case "N2AJ"
CurrentTopic = 1
Case "N9421D"
CurrentTopic = 2
Case "N74NM"
CurrentTopic = 3
Case "N146K"
CurrentTopic = 4
Case Else
CurrentTopic = 1
End Select
MsgBox CurrentTopic

End Sub

 

[Norman Jones]

Hi Michael,

Forgot to change:
Airplane = Cells(1, 1).Value
back to your address:
Airplane = Cells(30, 1).Value

 

[Michael Vaughan]

Hi Norman,

Thanks for the help. I got it to work now, but had problems with the code
that you gave. Initially I had the code in:

Private Sub UserForm_Initialize()
' Executed before the form is shown
Dim Row As Integer
Set HelpSheet = ThisWorkbook.Sheets(HelpSheetName)
TopicCount =
Application.WorksheetFunction.CountA(HelpSheet.Range("A:A"))
For Row = 1 To TopicCount
ComboBoxTopics.AddItem HelpSheet.Cells(Row, 1)
Next Row
ComboBoxTopics.ListIndex = 0

Dim Airplane As String
Airplane = Cells(10, 5).Value
If Airplane = "N2AJ" Then CurrentTopic = 1
If Airplane = "N9421D" Then CurrentTopic = 2
If Airplane = "N74NM" Then CurrentTopic = 3
If Airplane = "N146K" Then CurrentTopic = 4
UpdateForm
End Sub

When I added your Sub Tester() within it, it didn't like that. So, I guess
you can't have a sub within a sub??? Should I have put Tester right before
the UpdateForm (see above) to run the subroutine Tester??

Sub Tester()
Dim Airplane As String
Dim CurrentTopic As Long
Airplane = Cells(1, 1).Value

 

[Norman Jones]

Hi Michael,

Should I have put Tester right before
the UpdateForm (see above) to run the subroutine Tester??

It would have been clearer If I had named my sub as Sub
UserForm_Initialize(). The code between Sub Tester() and End Sub, was a
suggestion to replace your shown code.

I am pleased that your code now works but a couple of observations:

It is good programming practice explicitly to declare all variables. In this
connection, see Chip Pearson's detailed observations:

http://www.cpearson.com/excel/DeclaringVariables.htm
and
http://www.cpearson.com/excel/variables.htm

Additionally, from the perspectives of readability and code maintenance, I
would advocate that all variables be declared at the top of the procedure.

I replaced your multiple If ... Then form with a Select Case structure as a
matter of personal predelection rather than to suggest any error.


Regards,
Norman