option buttons 1

[Yogi39]

I would like to use 3 option buttons to hide/display rows depending on which option button is selected.....

How would I trigger macro ?
how would I know which option button is checked ?

 

[SkipVought]

Yogi39,

Form control or Control Toolbox?

If COntrol Toolbox, in the OptionButton_Click event, check the state of the button and if the state meets the requirement then hide or unhide the rows.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Yogi39]

Hi Skip...thanks for the reply....

I put 3 optionbuttons on my worksheet(forms/optionbutton)
I would like the following :

Sub OptionButton3861_Click()
if OptionButton3861 = true do nothing 
else 
ActiveSheet.Cells.EntireRow.Hidden = False
End Sub

 

[SkipVought]

Sub OptionButton1_Click()
    If ActiveSheet.Shapes("Option Button 1").DrawingObject = 1 Then
        MsgBox "T"
    Else
        MsgBox "F"
    End If
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Yogi39]

Thanks...works great...
PS:
I need a function to determine if a value of a cell is numeric and if not just extract the numbers in order from left to right ....

 

[SkipVought]

Function GetDigits(sValue as String)
  If Not IsNumeric(sValue) then
    for i = 1 to len(sValue)
      b = mid(sValue, i, 1)
      Select Case b
        case "0" to "9"
          GetDigits = GetDigits & b
      end select
    next
  end if
end function

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Yogi39]

getting a error when I try to incorporate....

Sub OptionButton3862_Click()
Application.ScreenUpdating = False
For Each r In Sheets("Master").Range("A:A")
If r.Row > 4 Then
If r = "" Then Exit For
If IsNumeric(r) And r >= 2000 Then
r.EntireRow.Hidden = True
Else
If GetDigits(r) >= 2000 Then
r.EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
Function GetDigits(sValue As String)
  If Not IsNumeric(sValue) Then
    For i = 1 To Len(sValue)
      b = Mid(sValue, i, 1)
      Select Case b
        Case "0" To "9"
          GetDigits = GetDigits & b
      End Select
    Next
  End If
End Function

 

[SkipVought]

can't use r alone. r is a range object. in the context, a string is needed, hence r.value...

Sub OptionButton3862_Click()
    Application.ScreenUpdating = False
    For Each r In Sheets("Master").Range("A:A")
        If r.Row > 4 Then
            If r.Value = "" Then Exit For
            If IsNumeric(r) And r >= 2000 Then _
            r.EntireRow.Hidden = True
        Else
            If GetDigits(r.Value) >= 2000 Then _
            r.EntireRow.Hidden = True
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Yogi39]

Thanks Skip...!