I have a user form that activates when a cell in a specific range is selected. The userform specifies an insurer with 3 optionbuttons and therapy services rendered with 3 checkboxes. When I run the form, it will save into the selected cell a string value such as Medicare A: Physical Therapy Occupational Therapy Speech therapy.
The code works as intended, but what I am now running into trouble doing is the reverse. I want to be able to click on the cell and have it show the user form with the buttons and checkboxes selected based on the string value in the cell.
Here is my userform:
Here is my efforts trying to show the userform with the PT value triggering optionbutton1 to change to true.
The code works as intended, but what I am now running into trouble doing is the reverse. I want to be able to click on the cell and have it show the user form with the buttons and checkboxes selected based on the string value in the cell.
Here is my userform:
Code:
Private Sub CommandButton1_Click()ActiveSheet.Unprotect
Dim Insurer As String
Dim PT As String
Dim OT As String
Dim ST As String
If OptionButton1.Value = True Then Insurer = "A: "
If OptionButton2.Value = True Then Insurer = "HMO: "
If OptionButton3.Value = True Then Insurer = "B: "
If CheckBox1.Value = True Then PT = "PT "
If CheckBox2.Value = True Then OT = "OT "
If CheckBox3.Value = True Then ST = "ST "
If Not Me.OptionButton1 And Not Me.OptionButton2 And Not Me.OptionButton3 Then
Me.Hide
MsgBox "You must select an insurer.", vbOKOnly, "Selection Required"
Me.Show
Else
ActiveCell = Insurer & PT & OT & ST
Unload Me
With Worksheets("Sheet1")
.Activate
.Range("A1").Select
End With
ActiveSheet.Protect
End If
End Sub
Private Sub CommandButton2_Click()
ActiveSheet.Unprotect
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
Selection.ClearContents
Unload Me
ActiveSheet.Protect
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Here is my efforts trying to show the userform with the PT value triggering optionbutton1 to change to true.
Code:
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CellText As String
On Error Resume Next
If Target.Column = 5 And Target.Row < 64 And Target.Row > 1 Then
UserForm1.Show
CellText = ActiveCell.Text
If InStr(1, CellText, "PT", vbTextCompare) > 0 Then CheckBox1.Value = True
End If
End Sub