A
Axel
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"
' UsrFrmVarcptionButton1 = True
' Case Is = "=$C$1-365"
' UsrFrmVarcptionButton2 = True
' Case Is = "=$C$1-730"
' UsrFrmVarcptionButton3 = True
'End Select
GoTo Lastline
errorline:
MsgBox "Bare tall mellom 1 og 1000 kan brukes som radnummer"
Lastline:--
Copy & paste developer
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"
' UsrFrmVarcptionButton1 = True
' Case Is = "=$C$1-365"
' UsrFrmVarcptionButton2 = True
' Case Is = "=$C$1-730"
' UsrFrmVarcptionButton3 = True
'End Select
GoTo Lastline
errorline:
MsgBox "Bare tall mellom 1 og 1000 kan brukes som radnummer"
Lastline:--
Copy & paste developer