Using "After Update

[Denny57]

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

Dropbox

www.dropbox.com

 

[Denny57]

Once again thank you for your help, however, once I added details into the worksheet some of the code does not work.

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.

Dropbox

www.dropbox.com

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

I have managed to get the following VLookup code to finally be accepted, however the control shows the numeric date value. How might I get this to display the visible date in the worksheet. Thanks again

 

[myall_blues]

Being in Australia I'm not 100% sure if what I'm dealing with is regional issues, but from what I can tell the issue arises because the values in column M are text, as a result of the pound symbol formatting.
For example, if you put the formula

=ISTEXT(M2)

in N2 it returns true, so your SUMIFS in the code returns zero from summing text.

The solution is to make the values in column M plain numbers, in which case it works for me.

The problem with this part of the code:

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

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

as before, is that txtGrossPayWork is a text control and txtGrossPayWork.value is text, so you cannot declare x as double and then assign it directly to the value of txtGrossPayWork. You have to convert it to double, like so:

x=CDbl(txtGrossPayWork.Value)

Refer my previous code for the same variable at post #2 in this thread.

 

[Denny57]

I have now resolved the VLookup issue.

I just cannot understand why the SUMIFS are not totalling for the

Being in Australia I'm not 100% sure if what I'm dealing with is regional issues, but from what I can tell the issue arises because the values in column M are text, as a result of the pound symbol formatting.
For example, if you put the formula

=ISTEXT(M2)

in N2 it returns true, so your SUMIFS in the code returns zero from summing text.

The solution is to make the values in column M plain numbers, in which case it works for me.

The problem with this part of the code:

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

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

as before, is that txtGrossPayWork is a text control and txtGrossPayWork.value is text, so you cannot declare x as double and then assign it directly to the value of txtGrossPayWork. You have to convert it to double, like so:

x=CDbl(txtGrossPayWork.Value)

Refer my previous code for the same variable at post #2 in this thread.

Thank you. I have manually changed the format of the details in Columns L & M to numeric and the correct details are now visible. I just need to change the code to upload numeric values for these columns

 

[Joe4]

Denny57,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution, or a post saying that you solved it, but did not include the solution in the post). Any post marked as the solution MUST actually contain the solution.

When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.