VBA – Update TextBox based on value entered / calculated in another Textbox

wcm69

Board Regular
Joined
Dec 25, 2016
Messages
112
Hi All

I have created a (time sheet) userform with 7 textboxes each representing a day of the week (Monday – Sunday). I have a further 4 textboxes for the Daily Rate, Gross Pay, Tax Paid and final ‘Net Pay’.

I have restricted entry in the 7 textboxes to 1 or 0. - 1 for a day worked or 0 for a day not worked.
I would like (on the change event) as the user enters 1 - the Gross, Tax and the Net Pay textboxes are automatically updated.

This requires each day worked to be multiplied by the ‘Daily Rate’ (txtRate).

I have managed to achieve the above for the first textbox (Monday) but have not been able to do so for the remaining 6 textboxes (Tuesday – Sunday).

I’ve looked on line and seen others have had similar issues but none of the solutions have worked for my purpose. If anyone has a solution the working code I’ve used in textbox 1(txtDay1) is:

Private Sub txtDay1_Change()

Me. txtDay 1.MaxLength = 1
On Error Resume Next

If Me.txtCIS.Value = "No" Or Me.txtCIS.Value = "Pending" Then

Me.txtGross.Value = Sum * Me.txtRate.Value
Me.txtTax.Value = Me.txtGross * 30 / 100
Me.txtNet.value = Me.txtGross.Value – Me.txtTax.Value

Else

Me.txtGross.Value = Sum * Me.txtRate.Value
Me.txtTax.Value = Me.txtGross * 20 / 100
Me.txtNet.value = Me.txtGross.Value – Me.txtTax.Value

End If

Me.txtDay2.Enabled = True
Me.txtDay2.SetFocus

End Sub

I’d really appreciated any help on this one as I’m slowly improving my VBA knowledge but still have a lot to learn, and as usual I’m always grateful to those with greater knowledge who take their time to help those of us with less.

Many thanks in advance :)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Not totally understanding your issue. Is it that when you put a 1 in Monday, it does the calculations...but when you put a 1 in Tuesday, it should be, essentially, the same calculations + the results from Monday's calculation?
 
Upvote 0
Hi DushiPunda,

Thank you for taking the time to respond.

You understanding (above) is correct.
Currently when I put a 1 in Monday, it does the calculations...but then does not update (txtGross, txtTax or txtNet)when I put a 1 in Tuesday. As you've said it should essentially carry out, the same calculations + the results from Monday's calculation.

If you've got a solution for the above I'd be eternally grateful, as I've been trying to resolve this issue for days now .:confused:

Thank you in advance
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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