Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Qn: SpinButton1.Max not working??
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 71
Default Qn: SpinButton1.Max not working??
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("Mario n").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm

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

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

If ComboBox1 = "Hancock County" Then County = "Hancock"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Hanco ck").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("Mario n").Range("A:A"))
.Min = 1
.Value = 1
End With
End Sub



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default SpinButton1.Max not working??
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" wrote in message
...
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("Mario n").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm

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

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

If ComboBox1 = "Hancock County" Then County = "Hancock"
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Hanco ck").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("Mario n").Range("A:A"))
.Min = 1
.Value = 1
End With
End Sub





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 71
Default SpinButton1.Max not working??
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

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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default SpinButton1.Max not working??
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)

"Michael Vaughan" wrote in message
...
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

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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate working days but change working week SamB Excel Discussion (Misc queries) 1 September 1st 08 09:17 PM
Making weekend days working days - the system cuts the working tim Fluffy Excel Discussion (Misc queries) 1 May 30th 08 10:02 PM
macro was working, now it's not working RichardO[_11_] Excel Programming 2 June 9th 04 06:27 AM
Macro working in Excel 2003; not working in Excel 2000 Leslie Barberie Excel Programming 5 May 20th 04 07:51 PM
Adding sales from a non working day to the previous working day Alex Excel Programming 1 September 19th 03 08:48 AM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement