Toggle Buttons

[mrroland]

Hi,

On top of a workbook i have created some navigation buttons to automate scrolling. As you can see the code is very simple but it works. The problem is that i would like to higlight the button that is pressed. So it the user pressed Inventory the inventory button is highlighte and all the other buttons are grey. i thought the toggle button would do this but than i can have a situation where multiple buttons are highlighted. Is there a way to figure out which button is active?

Private Sub Finance_Click()
ActiveWindow.FreezePanes = False
Range("a12").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll down:=100
End Sub

Private Sub Inventory_Click()
ActiveWindow.FreezePanes = False
Range("a12").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll down:=200
End Sub

Private Sub Performance_Click()
ActiveWindow.FreezePanes = False
Range("a12").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll down:=300
End Sub

Cheers,
Roland

 

[Andrew Poulsom]

Something like this?

Private Sub Finance_Click()
    ActiveWindow.FreezePanes = False
    Range("A12").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll down:=100
    With ActiveSheet
        .OLEObjects("Finance").Object.BackColor = RGB(0, 255, 0)
        .OLEObjects("Inventory").Object.BackColor = -2147483633
        .OLEObjects("Performance").Object.BackColor = -2147483633
    End With
End Sub

Private Sub Inventory_Click()
    ActiveWindow.FreezePanes = False
    Range("A12").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll down:=200
    With ActiveSheet
        .OLEObjects("Inventory").Object.BackColor = RGB(0, 255, 0)
        .OLEObjects("Finance").Object.BackColor = -2147483633
        .OLEObjects("Performance").Object.BackColor = -2147483633
    End With
End Sub

Private Sub Performance_Click()
    ActiveWindow.FreezePanes = False
    Range("A12").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll down:=300
    With ActiveSheet
        .OLEObjects("Performance").Object.BackColor = RGB(0, 255, 0)
        .OLEObjects("Finance").Object.BackColor = -2147483633
        .OLEObjects("Inventory").Object.BackColor = -2147483633
    End With
End Sub

 

[mrroland]

Thanks Andrew,

I already had moved on to a combo box but your solution is perfect.

Many, many thanks

Roland

 

[Philippe1990]

Andrew,

How should i use the code for highlighting buttons, on the following code?

Sub FlowchartTerminator17_Click()
HideRow "Open"
End Sub
Sub HideRow(ByVal txt As String)
Dim r As Range
For Each r In Range("A2:A122")
If (r.Value = "") + (r.Value Like "*" & txt & "*") Then
r.EntireRow.Hidden = Not r.EntireRow.Hidden
End If
Next
End Sub


thanks for your help

 

[Andrew Poulsom]

Is it?

Sub FlowchartTerminator17_Click()
    HideRow "Open"
    Me.OLEObjects("FlowchartTerminator17").Object.BackColor = RGB(0, 255, 0)
End Sub

 

[Philippe1990]

Andrew,

seems it does not work.
"Compile error: invalid use of ME keyword"

don't know what that means, i kind of new to vba...

 

[Andrew Poulsom]

Where is the code you posted and what type of control is FlowchartTerminator17? My code assumes that it is in the module for a worksheet that contains an ActiveX CommandButton.

 

[Philippe1990]

The code was originaly for a commandbutton,
i've modified it for my own use of it.

i have 4 different flowcharts within one textbox each assigned to a sepparate macro. The following code is used for the macro.

Sub FlowchartTerminator18_Click()
HideRow "Open Confirmed Dispatch date by Supplier"
End Sub
Sub HideRow(ByVal txt As String)
Dim r As Range
For Each r In Range("A2:A122")
If (r.Value = "") + (r.Value Like "*" & txt & "*") Then
r.EntireRow.Hidden = Not r.EntireRow.Hidden
End If
Next
End Sub

See attached picture for the buttons i used.

thanks

 

[Philippe1990]

http://www.mypicx.com/01192011/flowchartterminator/ go to for the picture