'automating' a process on a userform with VBA

[Mattlake]

Hi

I have a VBA script that works but i want to automate a section that requires a button to be clicked.

So i have pasted the script that works when it is connected to a button into the script of the button to press to move the data and to clear the form, but it doesn't do anything.

any clue?
below is the full script that works, I want to add the sum_totalday_Click() script into the Sub cmd_enter_Click()

Thank you for any assistance (even if it cannot be done)

Regards
Matthew

Private Sub openform_button_Click()

Call UserForm_Initialize

End Sub

Private Sub Label1_Click()

End Sub

Private Sub Opt_uk_Click()

End Sub

Sub sum_totalday_Click()

    If IsDate(Text_datefrom) And IsDate(Text_dateto) Then
        Text_dayno = DateValue(Text_dateto) - DateValue(Text_datefrom) + 1
    ElseIf IsDate(Text_datefrom) And Not IsDate(Text_dateto) Then
        Text_dayno = 1
    Else
        Text_dayno = ""
    End If
    
End Sub


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, 5).Value = Text_dayno.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

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", "-")

' format_sort_dates Macro
'
    If IsDate(Text_datefrom) And IsDate(Text_dateto) Then
        Text_dayno = DateValue(Text_dateto) - DateValue(Text_datefrom) + 1
    ElseIf IsDate(Text_datefrom) And Not IsDate(Text_dateto) Then
        Text_dayno = 1
    Else
        Text_dayno = ""
    End If
'
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "C1:C51"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
Me.combo_centre.Value = ""
Me.Text_course.Value = ""
Me.Text_datefrom.Value = ""
Me.Text_dateto.Value = "N/A"
Me.Text_dayno.Value = ""
Me.Text_studentno.Value = ""
Me.Text_staffno.Value = ""
Me.Text_where.Value = ""
Me.Opt_uk.Value = "-"
Me.opt_eu.Value = "-"
Me.opt_usa.Value = "-"
    
'Set Focus on combo_centre
combo_centre.SetFocus '


    
ThisWorkbook.Save

End Sub

Private Sub text_datefrom_AfterUpdate()
On Error Resume Next
Me.Text_datefrom = CDate(Me.Text_datefrom)
Me.Text_datefrom = Format(Text_datefrom.Text, "dd mmm yy")
End Sub

Private Sub text_dateto_AfterUpdate()
On Error Resume Next
Me.Text_dateto = CDate(Me.Text_dateto)
Me.Text_dateto = Format(Text_dateto.Text, "dd mmm yy")
End Sub


Private Sub UserForm_Initialize()
Me.combo_centre.Value = ""
Me.Text_course.Value = ""
Me.Text_datefrom.Value = ""
Me.Text_dateto.Value = "N/A"
Me.Text_dayno.Value = ""
Me.Text_studentno.Value = ""
Me.Text_staffno.Value = ""
Me.Text_where.Value = ""
Me.Opt_uk.Value = "-"
Me.opt_eu.Value = "-"
Me.opt_usa.Value = "-"

End Sub

Private Sub UserForm_Terminate()

' newwrap Macro
'

'
    Range("H:H,B:B,A:A").Select
    Range("A1").Activate
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .wraptext = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("23:23").EntireRow.AutoFit
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "C1:C48"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Selection.End(xlDown).Select

End Sub

 

[bobsan42]

what exactly do you expect to happen?
you are actually changing Text_dayno after you have transferred its previous value to the sheet. maybe you should move it a bit forward in the code ?
BTW, instead of confusing yourself with mixing too much code together you can instead do that:

Call sum_totalday_Click

 

[Mattlake]

thank you so much

 

[Peter_SSs]

thank you so much

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Mattlake]

ahh i will get it right one day

Thank you