Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
activate optionbutton in a userform based o a condition formatting
I made an condition formatting from a userform with this mackro:
Sub optionbutton() If OptionButton1 = True Then GoTo Opt1 Else GoTo Opt2 Opt1: Range("H" & CStr(iCtr + 3)).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$C$1-174" With Selection.FormatConditions(1).Font .Strikethrough = False .ColorIndex = 5 End With GoTo OptEnd Opt2: If OptionButton2 = True Then GoTo Opt3 Else GoTo Opt4 Opt3: Range("H" & CStr(iCtr + 3)).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$C$1-365" With Selection.FormatConditions(1).Font .Strikethrough = False .ColorIndex = 7 End With GoTo OptEnd Opt4: If OptionButton3 = True Then Range("H" & CStr(iCtr + 3)).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$C$1-730" With Selection.FormatConditions(1).Font .Strikethrough = False .ColorIndex = 7 End With OptEnd: End Sub That works fine But am not able to create a macro that activate the correct optionbutton based on the formatting in the cell. When I open the userform and select the row from combobox. I want the correct optionbutton to be activated based on the format formula Have tryed several solutions, but my skills just not good enough Private Sub ComboBox1_Change() Dim iCtr As Integer Dim FC As FormatConditions FC = xlCellValue On Error GoTo errorline iCtl = ComboBox1.Value ComboBox2.Text = Range("B" & CStr(3 + iCtl)) TextBox1.Text = Range("C" & CStr(3 + iCtl)) TextBox2.Text = Range("D" & CStr(3 + iCtl)) TextBox3.Text = Range("E" & CStr(3 + iCtl)) TextBox4.Text = Range("F" & CStr(3 + iCtl)) TextBox5.Text = Range("G" & CStr(3 + iCtl)) Label19 = Range("H" & CStr(3 + iCtl)) ' Set target = Range("H" & CStr(3 + iCtl)) ' Select Case FC ' Case Is = "=$C$1-157" ' UsrFrmVarco.OptionButton1 = True ' Case Is = "=$C$1-365" ' UsrFrmVarco.OptionButton2 = True ' Case Is = "=$C$1-730" ' UsrFrmVarco.OptionButton3 = True 'End Select GoTo Lastline errorline: MsgBox "Bare tall mellom 1 og 1000 kan brukes som radnummer" Lastline:-- Copy & paste developer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condition Formatting based on If Then | Excel Worksheet Functions | |||
Formatting based on a condition | Excel Programming | |||
Conditional Formatting (Hightlight row based on one condition) | Excel Discussion (Misc queries) | |||
optionbutton in userform | Excel Programming | |||
Showing a userform based on a condition. | Excel Programming |