UserForm Checkbox

[G1032]

Hi folks

I have a simple UserForm which has 3 checkboxes.
Checkboxes labelled "Good" , "Average" and "Bad"
When "Good" is checked, the value "1" is entered into a cell. If "Average" is checked then the value "0.5" is entered and "0" is entered when "Bad" is checked.

The problem is this......I need all 3 checkboxes to be clear when I open up the UserForm. But when I add in a line of code to clear the checkbox it also enters the 1 or 0.5 or 0.

How do I check a box, get the data entered into the cell I want, and then uncheck that same box without writing any further data?
Thanks

Private Sub CheckBox14_Click()With Sheets("RawData").Select
Range("E65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = "1"
Sheets("Home").Select
Range("A1").Select
End With
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
try using an AfterUpdate event instead

Private Sub CheckBox14_AfterUpdate()

 

[mikerickson]

Or You could do this

Private Sub TextBox14_Change()
    Sheets("RawData").Range("E65536").End(xlUp).Offset(1, 0).Value = CheckBoxTextValue
End Sub

Private Sub TextBox15_Change()
    Sheets("RawData").Range("E65536").End(xlUp).Offset(1, 0).Value = CheckBoxTextValue
End Sub

Private Sub TextBox16_Change()
    Sheets("RawData").Range("E65536").End(xlUp).Offset(1, 0).Value = CheckBoxTextValue
End Sub

Function CheckBoxTextValue() as String
    If CheckBox14.Value Then
        CheckBoxTextValue = "1"
    ElseIf CheckBox15.Value Then
        CheckBoxTextValue = ".5"
    ElseIf CheckBox16.Value Then
        CheckBoxTextValue = "0"
    Else
        CheckBoxTextValue = vbNullString
    End If
End Function

 

[MickG]

Another option:-

[COLOR="Navy"]Sub[/COLOR] chk(Cb)
[COLOR="Navy"]Dim[/COLOR] cBox [COLOR="Navy"]As[/COLOR] Control, Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cBox [COLOR="Navy"]In[/COLOR] Me.Controls
[COLOR="Navy"]If[/COLOR] TypeName(cBox) = "CheckBox" [COLOR="Navy"]Then[/COLOR]
   [COLOR="Navy"]If[/COLOR] Not cBox.Name = Cb.Name [COLOR="Navy"]Then[/COLOR] cBox.Value = False
   [COLOR="Navy"]If[/COLOR] cBox.Name = Cb.Name [COLOR="Navy"]Then[/COLOR]
        Lst = Range("E" & Rows.Count).End(xlUp).Offset(1).Row
              [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Cb.Name
                    [COLOR="Navy"]Case[/COLOR] "CheckBox1": Num = 1
                    [COLOR="Navy"]Case[/COLOR] "CheckBox2": Num = 0.5
                    [COLOR="Navy"]Case[/COLOR] "CheckBox3": Num = 0
                [COLOR="Navy"]End[/COLOR] Select
                Cells(Lst, "E") = Num
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] cBox
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox1_AfterUpdate()
chk CheckBox1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] CheckBox2_AfterUpdate()
chk CheckBox2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox3_AfterUpdate()
chk CheckBox3
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[G1032]

Thanks folks for your replies
I'll check them out later when I get a chance. Cheers!