Using VB Forms With Excel

[amram2002]

Im doing a project for my IT class, its a spreadsheet to keep track of scores for a sporting event. What I want todo is have a VB userform display and then from that you can enter contestant data and event data.

But what im stuck on is how to move data say a name of a contestant from a textbox onto the spreadsheet? anyone got any ideas?

[da_silvy]

In VBA or VB?

[amram2002]

VB I think

[alex_read]

By Userform he's talking about the vb forms within Excel, so it'd be VBA there...

Okay, shove a textbox & a command button on a form, then add this code & run it. This'll add whatever's typed in the textbox into the first sheet in the active workbook, in the first cell 'A1':

Private Sub CommandButton1_Click()
    Worksheets(1).Range("A1").Value = TextBox1.Text
End Sub

[da_silvy]

I think you'd have to use .FormulaR1C1 though...

Value gives some dodgy stuff etc

[amram2002]

Originally posted by alex_read
By Userform he's talking about the vb forms within Excel, so it'd be VBA there...

Okay, shove a textbox & a command button on a form, then add this code & run it. This'll add whatever's typed in the textbox into the first sheet in the active workbook, in the first cell 'A1':

Private Sub CommandButton1_Click()
    Worksheets(1).Range("A1").Value = TextBox1.Text
End Sub

Thanks, that worked ill let you know if I have any more problems.

[alex_read]

Thanks, that worked ill let you know if I have any more problems.

uh, gee thanks - um, guess I'll look forward to it!

[amram2002]

Ok, new problem ive got some check boxes which when clicked enters a contestant for a event. The problem is when I unclick them they are entered again?

[alex_read]

It's usually best to start another post if there's a different question subject matter - even if it's from the same project, you'll have more replies this way

Ok, new problem ive got some check boxes which when clicked enters a contestant for a event. The problem is when I unclick them they are entered again?

I take it you're performing the code to enter a contestant within the Checkbox_Click() event? If this is the case, the click event is triggered when the user clicks on the checkbox - whether they tick the box or remove the tick, so you might want to put something like this in:

Private Sub CheckBoxNameHere_Click()
    If CheckBoxNameHere.value = 1 Then '(true/ticked)
        'Execute the enter contestant bit here
    End If
End Sub

[amram2002]

Ok heres what ive got and it doesn't work

Private Sub CheckBox1_Click()
If CheckBox1.Value = 1 Then '(true/ticked)
            Selection.EntireRow.Insert
Worksheets(3).Range("A2").Value = txtid.Text
Worksheets(3).Range("B2").Value = txtfirstname.Text
    End If
End Sub

[alex_read]

Does it throw up an error message?

Does changing the top line to this make a difference?

If CheckBox1.Value <> 0 Then

[amram2002]

Nope, that doesn't help I don't get a error message I just select the check box and nothing happens.

[amram2002]

Ok, ive kinda got it working ive used this code,

Private Sub CheckBox1_Click()
If CheckBox1.Value = 0 Then
Selection.EntireRow.Insert
Worksheets(3).Range("A2").Value = txtid.Text
Worksheets(3).Range("B2").Value = txtfirstname.Text
End If
End Sub

It works but only if I select then unselect, hopefully you can tell me whats wrong from that.

[alex_read]

huh, weird, what about using this one then:

Private Sub Check1_Click()
    If Check1.Value = vbChecked Then
        MsgBox "Checkbox is ticked"
    Else
        MsgBox "Checkbox Not ticked"
    End If
End Sub

[amram2002]

That code works but its backwards, when the tick box is not ticked it says it is?

[amram2002]

Don't worry ive got it to work now