Using "After Update

Denny57

Active Member
Joined
Nov 23, 2015
Messages
255
Office Version
  1. 365
Platform
  1. Windows
I am having some issues with a sub routine which I am hoping will update a number of User Form controls once a value is added to another control.

In thre attttached file I have "commented out" the sub-routine. I would welcome any assistance with the correct Syntax.

I would also appreciate some assitance with the final line in the penultimate sub-routine which requires a VLookup that does not want to function.

Many thanks

 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
None of your subs are commented out. Which one is causing a problem?

Regarding the VLOOKUP issue, try making amendments as follows:
1. Populate the combobox with your list of values.
VBA Code:
Private Sub UserForm_Initialize()

    Set wsDailyRecords = ThisWorkbook.Worksheets("Daily Records")
    '
    ' Set cboSearchByPayMonth combobox parameters and source
    '
    cboSearchByPayMonth.ColumnCount = 1
    cboSearchByPayMonth.ColumnWidths = "60"
    cboSearchByPayMonth.List = Sheet4.Range("C2:C29").Value
    Call cmdClearForm_Click
End Sub

2. Modify the VLOOKUP statement.
VBA Code:
 Private Sub cmdMonthSearch_Click()
   
   txtPayMonthMonth.Value = cboSearchByPayMonth.Value
   txtPayableHoursWork = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth), "#,##0.00")
   txtGrossPayWork = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth), "£#,##0.00")
   txtPayableHoursLeave = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth), "#,##0.00")
   txtGrossPayLeave = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth), "£#,##0.00")
   
   Dim x As Double, Y As Double             'Since you have declared x and y as double, you must convert txtGrossPayWork and txtGrossPayLeave
                                            ' to double when you assign them to x and y, or a type mismatch occurs.
   x = CDbl(Mid(txtGrossPayWork.Value, 2))
   Y = CDbl(Mid(txtGrossPayLeave.Value, 2))
   
   txtTotalGrossPay.Value = Format(x + Y, "Currency")
   
   txtPayDate.Value = Format(CDate(Application.VLookup(cboSearchByPayMonth.Value, Sheet5.Range("A2:D30"), 2, False)), "dd/mm/yyyy")
   
   End Sub

Since you are using 365 I'd recommend converting the VLOOKUP to XLOOKUP, unless you have users that are not using 365 yet. The XLOOKUP syntax would be"
VBA Code:
txtPayDate.Value = Format(CDate(Application.XLookup(cboSearchByPayMonth.Value, Sheet5.Range("A2:A30"), Sheet5.Range("B2:B30"), "Not found")), "dd/mm/yyyy")
 
Upvote 0
Apologies, the changes I made must not have saved.

Private Sub txtFinishTime_AfterUpdate()

With Application
Me.txtGrossHours.Value = Application.Text((txtFinishTime.Value - txtStartTime.Value), "hh:mm")
Me.txtGrossHoursDecimal.Value = Application.Text(txtGrossHours.Value * 24, ".00")
Me.txtBreakAllowance.Value = .IfError(.VLookup(Me.txtGrossHoursDecimal), Sheet3.Range("A3:H42"), 6, False)
Me.txtPayableHours.Value = Application.Text((txtGrossHoursDecimal.Value - txtBreakAllowance.Value))
Me.txtGrossPay.Value = Format(txtPayableHours.Value * txtHourlyRate.Value, "Currency")

End With
End Sub

My aim is to populate controls in a User form before passing these to a worksheet. Each action is dependent on calculating the difference between tstFinish Time and txtStartTime. Maybe I should use a separate sub for each.

1) Calculate the difference between txtFinishTime and txtStartTime in hh:mm format
2) Convert the result of 1) into decimal format
3) Look up the txtBreakAllowance value of 2)
4) Deduct the result of 2) less 3)
5) Calculate the value of 4) multiplies by the value of txtHourlyRate in "Currency" format

Why? I am looking to avoid populating hundred of lines in the Daily Records with formulas which might have variables that change.

Note, I fully expect additional problems which I will investigate once I have successfully managed to populate the User Form fields correctly
 
Upvote 0
Myall_Blues

I have made a liitle progess. The User Form updates as required as far as "Gross Hours" which is where the code fails.

I have tried creating separate sub routines for the 4 actions which I hope would work, unfortunately this is not the case.

I have attached the latest version of the file and will provide any items I am able to resolve in the meantime

VBA Code:
Private Sub txtFinishTime_AfterUpdate()

    With Application
     Me.txtGrossHours.Value = Format(TimeValue(txtFinishTime.Text) - TimeValue(txtStartTime.Text), "hh:mm")
    'Me.txtGrossHoursDecimal = Format((txtGrossHours.Text) * 24, ".00")
    'Me.txtBreakAllowance.Value = .IfError(.VLookup(Me.txtGrossHoursDecimal), Sheet3.Range("A3:H42"), 6, False)
    'Me.txtPayableHours.Value = Application.Text((txtGrossHoursDecimal.Value - txtBreakAllowance.Value))
    'Me.txtGrossPay.Value = Format(txtPayableHours.Value * txtHourlyRate.Value, "Currency")
    
    End With
End Sub
Private Sub txtGrossHours_AfterUpdate()
    Me.txtGrossHoursDecimal = Format((txtGrossHours.Text) * 24, ".00")
End Sub
Private Sub txtGrossHoursDecimal_AfterUpdate()
    Me.txtBreakAllowance = IfError(VLookup(Me.txtGrossHoursDecimal), Sheet3.Range("A3:H42"), 6, False)
End Sub
Private Sub txtBreakAllowance_AfterUpdate()
    Me.txtPayableHours.Value = (txtGrossHoursDecimal.Value - txtBreakAllowance.Value)
End Sub
Private Sub PayableHours_AfterUpdate()
    Me.txtGrossPayValue = Format(txtPayableHours.Value * txtHourlyRate.Value, "Currency")
End Sub

 
Upvote 0
Without trying it, I expect that txtGrossHoursDecimal should be txtGrossHoursDecimal.Value. As soon as you put 'Me.' In front then you’re dealing with a form field so need to append a property.
Similarly for Me.txtBreakAllowance and Me.txtGrossPayValue


VBA Code:
Me.txtGrossHoursDecimal.Value = Format((txtGrossHours.Text) * 24, ".00")
 
Upvote 0
Hi, I have now managed to resolve most of the User Form update fields and as such the code has changed.

The only outstanding problem I have is with the Hourly Rate control.

The value of this should be shown as "Currency" and the value is dependent on a lookup
If cboScheduling value = "Work" or "Leave". The following code pulls the correct values but a number and not as Currency.

Also, the "Leave" value is the result of a calculation in the worksheet cells and shows multiple decimal places. Hopefully, by getting the control to be in Currency format, then this will become irrelevant
VBA Code:
Private Sub cboSchedulingType_AfterUpdate()
    With Application
   
        If cboSchedulingtype.Value = "Work" Then
            Me.txtHourlyRate.Text = (.VLookup(CLng(CDate(Me.txtDate)), Sheet2.Range("A2:J810"), 5, False))
        If cboSchedulingtype.Value = "Leave" Then
            Me.txtHourlyRate.Text = (.VLookup(CLng(CDate(Me.txtDate)), Sheet2.Range("A2:J810"), 7, False))
        End If
        End If
    End With
   End Sub

I cannot get this code to show as Currency.

Hoping you can help
 
Upvote 0
Maybe:
VBA Code:
Private Sub cboSchedulingType_AfterUpdate()  
        If cboSchedulingtype.Value = "Work" Then
            Me.txtHourlyRate.Text = FormatCurrency(Application.VLookup(CLng(CDate(Me.txtDate)), Sheet2.Range("A2:J810"), 5, False),2)
        End If
        If cboSchedulingtype.Value = "Leave" Then
            Me.txtHourlyRate.Text = FormatCurrency(Application.VLookup(CLng(CDate(Me.txtDate)), Sheet2.Range("A2:J810"), 7, False),2)
        End If
End Sub
 
Upvote 0
Solution
Once again thank you for your help, however, once I added details into the worksheet some of the code does not work.
VBA Code:
Private Sub cmdMonthSearch_Click()
txtPayMonthMonth.Value = cboSearchByPayMonth.Value
txtPayableHoursWork = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth.Value), "#,##0.00")
'txtGrossPayWork = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth.Value), "£#,##0.00")
txtPayableHoursLeave = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth.Value), "#,##0.00")
'txtGrossPayLeave = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth.Value), "£#,##0.00")

'Dim x As Double, y As Double
   ' x = txtGrossPayWork.Value
    'y = txtGrossPayLeave.Value

    'txtTotalGrossPay.Value = Format(x + y, "Currency")

End Sub

This code works fine in a previous version in a separate file. The only differences are:-
Source Columns have changed
In the previous file the values in Columns M & K were calculated by formulas in the cells and not uploaded from the User Form as they are in this file

I have commented out the two lines of code which are not working (they return a value of £0.00).
I am ssuming that the txtTotalGrossPay code will work once the two Gross Pay field are correctly populated.


I also have a further line of code that I would appreciate some help with.

VLOOKUP
txtPayDate should contain the result of a VLookup of Sheet 5 Columns 2 of the value of cboSearchByMonth. Unfortunately I cannot get the syntax of the code to be accepted.

Many thanks
 
Upvote 0

Forum statistics

Threads
1,226,264
Messages
6,189,928
Members
453,581
Latest member
Browny2821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top