ToggleButtons are strange

[Ken McLennan]

G'day there One and All,

I'm back once again =)

I have a userform with 3 togglebuttons - tBtn1, tBtn2, & tBtn3. I'm
trying to get them to work like OptionButtons, unsuccessfully I might
add.

I've tried linking them to 3 separate cells, although I initially got
these results when there were no ControlSources.

I have the following code in the userform's events:

Private Sub tbtn1_Click()
Me.tbtn1.Value = True
Me.tbtn2.Value = False
Me.tbtn3.Value = False
End Sub

Private Sub tbtn2_Click()
Me.tbtn1.Value = False
Me.tbtn2.Value = True
Me.tbtn3.Value = False
End Sub

Private Sub tbtn3_Click()
Me.tbtn1.Value = False
Me.tbtn2.Value = False
Me.tbtn3.Value = True
End Sub

I've found that if I click btn1 it works fine clicking btn2 works OK,
but I can't then go back to btn1. Likewise, btn3 is OK, but I can't then
go back to btn1 or btn2. Once I've clicked the next button, the previous
ones don't change when clicked. I would have thought the above would
just change everything as appropriate (silly me!!)

I've Googled all over the place, and found several entries in a few
forums asking similar questions. The trouble is, that of the questions I
found, none had received a response.

The Help page for ToggleButtons states that I can put them inside a
frame, although it doesn't specify what would change should that happen.
Well, I tried it and found that absolutely bugger all changed. It still
didn't work and didn't give me access to the GroupName property even
though it comes up in the editor's assistant. When I tried GroupName it
generated some kind of assignment error.

Has this matter been resolved anywhere? Is there someway that I can get
my 3 togglebuttons to work like an optiongroup?

See ya & thanks
Ken McLennan
Qld, Australia

 

[SOS]

Ken,

What I did was to paste your code to try it. I then inserted a brea
point at the beginning of each Sub and then clicked tbtn1 and used F
to step through the code it seemed to go round in circles for wha
seemed like ever, going from one sub to the next to the next and bac
to the first (never really seeming to get the end). I haven't bee
able to figure out the answer toy our problem but will keep on trying.

Regards

Seamu

 

[Doug Glancy]

Ken,

I got the same type results as SOS. I put a msgbox at the end of each
routine, e.g., msgbox "1", etc. As you said, button 1 worked fine, but when
I click #2 it keeps cycling through the #1 routine. It seems that changing
the value of the button calls the the click event for that button, thereby
sending you round and round. You can't disable events in a UserForm, so
that won't help.

I also found that putting them in a frame doesn't make them act like
optionbuttons either. FYI, putting optionbuttons in a frame makes them only
interact with the other optionbuttons in the frame - clicking one outside
the frame does not affect the ones in the frame. Can you just use
optionbuttons instead?

hth,

Doug Glancy

 

[CDrews]

Ok, I looked into your little problem and have a solution, ugly as it
is; I have named the togglebuttons simply TB1, TB2, and TB3. I have
also added a Function to take care of the resetting of the toggles not
clicked. Give it a try and let me know if it is what you were looking
for.
Private Sub TB1_Click()
If TB1.Value = False Then
GoTo Bottom
Else

Dim Caller As Integer
Dim TB1Stat, TB2Stat, TB3Stat As Boolean

Caller = 1

Call MakeChange(Caller)

Let TB1.Value = True
End If
Bottom:
End Sub


Private Sub TB2_Click()
If TB2.Value = False Then
GoTo Bottom
Else

Dim Caller As Integer
Dim TB1Stat, TB2Stat, TB3Stat As Boolean

Caller = 2


Call MakeChange(Caller)

End If
Bottom:
End Sub


Private Sub TB3_Click()
If TB3.Value = False Then
GoTo Bottom
Else

Dim Caller As Integer
Dim TB1Stat, TB2Stat, TB3Stat As Boolean

Caller = 3

Call MakeChange(Caller)

End If
Bottom:
End Sub


Function MakeChange(CalledFrom)
Select Case CalledFrom
Case 1
TB2.Value = False
TB3.Value = False
Case 2
TB1.Value = False
TB3.Value = False
Case 3
TB1.Value = False
TB2.Value = False
End Select

End Function

 

[CDrews]

I'm sorry. I left in the DIM for a few booleans I was using to test,
You can remove the following line from the click procedures

Dim TB1Stat, TB2Stat, TB3Stat As Boolean

 

[Ken McLennan]

G'day there Seamus,

What I did was to paste your code to try it. I then inserted a break
point at the beginning of each Sub and then clicked tbtn1 and used F8
to step through the code it seemed to go round in circles for what
seemed like ever, going from one sub to the next to the next and back
to the first (never really seeming to get the end). I haven't been
able to figure out the answer toy our problem but will keep on trying.

Well, you've gotten further than I did. I never even bothered with
the F9/F8 procedure. I was just confused right from the start =)

Thanks for having a look at it for me though.

See ya,
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there Doug,

the frame does not affect the ones in the frame. Can you just use
optionbuttons instead?

As it happens, I did use optionbuttons instead. I got a reply on
DevelopersDex that gave code to make it all work (thanks CDrews), but
I'd already implemented it with optionbuttons.

Then after all of that, I changed the way I worked with start &
end dates and now don't even have the optionbuttons =)

It's still got me puzzled as to why it shouldn't work. I know that
SOS found it it was calling the OnClick (thanks SOS), but I can't
imagine why it would do that. There's probably a reason but it beats me.
I just wish MSoft would give me a ring before they implement their ideas
<g>.

Thanks for the assistance
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there CDrews,

Ok, I looked into your little problem and have a solution, ugly as it
is; I have named the togglebuttons simply TB1, TB2, and TB3. I have
also added a Function to take care of the resetting of the toggles not
clicked. Give it a try and let me know if it is what you were looking
for.

Ugly or not, it works as I expected it should. There's obviously
something in the implementation of togglebuttons that interferes with
the logic to prevent the simple code I had from working.

IIRC the OptionButtons control in Access has an option to display
as ToggleButtons. Much easier, but does't happen in Excel.

Thanks very muchly for your hard work in figuring it out. I now
feel disappointed that I've changed the way I'm working with dates and
now don't need the toggle, or option buttons.

However, I'll be keeping the code on hand for future use, I can
assure you. My next project will use them on several forms.

Thanks once again
Ken McLennan
Qld, Australia