Excel VBA userform question

[Mattlake]

I hope someone can help me.

I am new to creating userforms, but I have managed to set one up, but I need to set up a 'submit' button that will in a sense save the data in a field and clear the form.

Below is the script I have written (most likely gibberish). when I press the button it says there is an issue and debug doesn't help me.

Please help

Thank you

Matt

Sub cmd_enter_Click()

Dim emptyRow As Long

'Make Sheet2 active
Sheet2.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = combo_centre.Value
Cells(emptyRow, 2).Value = Text_course.Value
Cells(emptyRow, 3).Value = Text_datefrom.Value
Cells(emptyRow, 4).Value = Text_dateto.Value
Cells(emptyRow, 6).Value = Text_studentno.Value
Cells(emptyRow, 7).Value = Text_staffno.Value
Cells(emptyRow, 8).Value = Text_where.Value
Cells(emptyRow, 9).Value = Opt_uk.Value
Cells(emptyRow, 10).Value = opt_eu.Value
Cells(emptyRow, 11).Value = opt_usa.Value

If Opt_uk.Value = True Then
Cells(emptyRow, 9).Value = "Yes"
Else
Cells(emptyRow, 9).Value = ""

If opt_eu.Value = True Then
Cells(emptyRow, 10).Value = "Yes"
Else
Cells(emptyRow, 10).Value = ""

If opt_usa.Value = True Then
Cells(emptyRow, 11).Value = "Yes"
Else
Cells(emptyRow, 11).Value = ""

'Set Focus on combo_centre
combo_centre.SetFocus

ThisWorkbook.Save
End Sub

 

[dmt32]

Hi welcome to forum

looking at your code you are missing some End Ifs around your OptionButtons

If Opt_uk.Value = True Then
    Cells(emptyRow, 9).Value = "Yes"
Else
    Cells(emptyRow, 9).Value = ""
End If

If opt_eu.Value = True Then
    Cells(emptyRow, 10).Value = "Yes"
Else
    Cells(emptyRow, 10).Value = ""
End If

If opt_usa.Value = True Then
    Cells(emptyRow, 11).Value = "Yes"
Else
    Cells(emptyRow, 11).Value = ""
End If

But you can write this differently

Cells(emptyRow, 9).Value = IIf(Opt_uk.Value, "Yes", "")

Cells(emptyRow, 10).Value = IIf(opt_eu.Value, "Yes", "")

Cells(emptyRow, 11).Value = IIf(opt_usa.Value, "Yes", "")

which should produce same result

Hope Helpful

Dave

 

[Mattlake]

that is so simple why couldn't i see it????

thank you so much I was almost about to give up.

I just need to program it to clear at the same time so i can enter additional rows in at once

 

[dmt32]

that is so simple why couldn't i see it????

thank you so much I was almost about to give up.

I just need to program it to clear at the same time so i can enter additional rows in at once

easily done but should never give up - help always here

code below is a stock approach used by many to clear a form for next use

Sub ResetForm()
  Dim ctrl As Control
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
            Case "CheckBox", "OptionButton", "ToggleButton"
                ctrl.Value = False
            Case "ComboBox", "ListBox"
                ctrl.ListIndex = -1
        End Select
    Next ctrl
End Sub

Just place a call to it at end of your enter code.

Dave

 

[Mattlake]

you are so helpful

Thank you

 

[dmt32]

you are so helpful

Thank you

That's very kind of you

If you are new to Userforms, this site always worth a visit:How to Create an Excel UserForm for Data Entry

Dave

 

[Mattlake]

Hi

Just a question is there a way of sorting the actual table so it goes in date order when I click the OK button?

Regards

Matthew

 

[dmt32]

Hi

Just a question is there a way of sorting the actual table so it goes in date order when I click the OK button?

Regards

Matthew

Its a new question but one suggestion - turn the macro recorder on & perform the required sort action .
This should give you some code you can adjust as required & which can called at end of your submit code.

Dave

 

[Mattlake]

perfect

I was trying to find a way to run the macro from the script forgetting that the macro was script that i could use.

Thank you again

you are saving my sanity