Qn: SpinButton1.Max not working??

[Michael Vaughan]

Hello to ALL Experts,

I have designed a UserForm that should display Doctors in different Counties
where I live. I have 4 different sheets for 4 different counties that
contain all the information. I have a ComboBox1 to select the different
counties. I also have a spin button that cycles through all the doctors. I
have about 10 doctors in County Marion, about 5 doctors in County Hendricks,
and I have about 3 or 4 in the other two. My UserForm1 code is below. What
the problem is, the userform comes up fine, and I have it default to Marion
County. But, it displays 1 of 1 doctors when there is 10 in Marion. Now,
if I click the spin button, then it cycles through to 2 of 10, 3 of 10 and
so on. But, if I click on Hendricks County or any other county, it will
only show the first Doctor and 1 of 1. I cannot display any other Doctors
in any county, including if I return back to Marion. Then it is like the
other counties, just the first record 1 of 1. Can somebody look at my code
below and see what I have done wrong??? Sure would appreciate it. Thanks
in advance... mv


Option Explicit
Dim County As String
Dim HelpTopic As Integer

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub UpdateForm()
HelpTopic = SpinButton1.Value
LabelAME.Caption = Sheets(County).Cells(HelpTopic, 1)
LabelDoc.Caption = Sheets(County).Cells(HelpTopic, 2)
LabelClass.Caption = Sheets(County).Cells(HelpTopic, 3)
LabelPL.Caption = Sheets(County).Cells(HelpTopic, 4)
LabelPhone.Caption = Sheets(County).Cells(HelpTopic, 5)
LabelAdd.Caption = Sheets(County).Cells(HelpTopic, 6)
LabelNote.Caption = Sheets(County).Cells(HelpTopic, 7)
Me.Caption = "AME's in " & County & " County (Doctor " & HelpTopic & "
of " & SpinButton1.Max & ")"
End Sub


Private Sub ComboBox1_Change()
If ComboBox1 = "Marion County" Then County = "Marion"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Marion").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm

If ComboBox1 = "Hendricks County" Then County = "Hendricks"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Hendricks").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm

If ComboBox1 = "Hamilton County" Then County = "Hamilton"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Hamilton").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm

If ComboBox1 = "Hancock County" Then County = "Hancock"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Hancock").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm
End Sub

Private Sub SpinButton1_Change()
HelpTopic = SpinButton1.Value
UpdateForm
End Sub

Private Sub UserForm_Initialize()
Me.Caption = APPNAME
ComboBox1 = "Marion County"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Marion").Range("A:A"))
.Min = 1
.Value = 1
End With
End Sub

 

[Bob Phillips]

Micheal,

here is some amended code that works. I have also loaded the counties into
the combobox to save having to type it in


Dim County As String
Dim HelpTopic As Integer
Dim aryMax(1 To 4) As Integer

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub UpdateForm()
With ComboBox1
County = Left(.Value, InStr(.Value, " ") - 1)
End With
HelpTopic = SpinButton1.Value
LabelAME.Caption = Sheets(County).Cells(HelpTopic, 1)
LabelDoc.Caption = Sheets(County).Cells(HelpTopic, 2)
LabelClass.Caption = Sheets(County).Cells(HelpTopic, 3)
LabelPL.Caption = Sheets(County).Cells(HelpTopic, 4)
LabelPhone.Caption = Sheets(County).Cells(HelpTopic, 5)
LabelAdd.Caption = Sheets(County).Cells(HelpTopic, 6)
LabelNote.Caption = Sheets(County).Cells(HelpTopic, 7)
Me.Caption = "AME's in " & County & " County (Doctor " & HelpTopic & "
of " & SpinButton1.Max & ")"
End Sub


Private Sub ComboBox1_Change()
With SpinButton1
.Min = 1
.Max = aryMax(ComboBox1.ListIndex + 1)
.Value = 1
End With

UpdateForm

End Sub

Private Sub SpinButton1_Change()
HelpTopic = SpinButton1.Value
UpdateForm
End Sub

Private Sub Userform_Activate()

With Application.WorksheetFunction
aryMax(1) = .CountA(Sheets("Marion").Range("A:A"))
aryMax(2) = .CountA(Sheets("Hendricks").Range("A:A"))
aryMax(3) = .CountA(Sheets("Hamilton").Range("A:A"))
aryMax(4) = .CountA(Sheets("Hancock").Range("A:A"))
End With

ComboBox1.ListIndex = 0

End Sub

Private Sub UserForm_Initialize()
Me.Caption = APPNAME
With ComboBox1
.AddItem "Marion County"
.AddItem "Hendricks County"
.AddItem "Hamilton County"
.AddItem "Hancock County"
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Michael Vaughan]

Hi Bob,

I replaced your code with what I had and I came up with a "Run-Time Error
'70', Permission Denied". I could not find out for the life of me what
caused that... so I put a "On Error Resume Next" in the Private Sub
UserForm_Initialize() and then it works perfectly!!!!! Outstanding, that is
exactly how I wanted to work. I guess I have to leave the On Error message
in, because without it I keep getting the Permission denied??? Any ideas on
that???

Thanks for you help in this... Michael

 

[Bob Phillips]

Hi Michael,

Not off the top, but I am intrigued.

Could you send me your workbook so that I could take a look? What version of
Excel? And note my spam filter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)