Worksheet_Change affecting data

stirlingmw1

Board Regular
Joined
Jun 17, 2016
Messages
53
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Morning All

I have a Workbook that I use to input and display/change data. The data is manipulated by 2 Userforms ("Input" & "Amend"). There are a few TextBoxes on these Userforms that are updated when the underlying Worksheet changes "Data", These TextBoxes display Time in hh:mm:ss format. I am using similar code within the Worksheet_Change event to update the TextBoxes in both Userforms. The problem I am having is if "Amend" Userform is activated the data that should be shown in these TextBoxes reverts to hh:mm:ss text as opposed to the time that was initially input into the Worksheet. The Worksheet data also changes to hh:mm:ss. If I comment out the code that updates the "Input" Userform then the "Amend" Userform works correctly.
Is there something I should be adding to the Worksheet_Change event that looks at which Userform is Active and only updates that Userform?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Amend.TextBox960.Text = Sheets("Data").Range("CU25").Text
Amend.TextBox961.Text = Sheets("Data").Range("CU26").Text
Amend.TextBox962.Text = Sheets("Data").Range("CU27").Text

Input.TextBox170.Text = Sheets("Data").Range("CU25").Text
Input.TextBox171.Text = Sheets("Data").Range("CU26").Text
Input.TextBox172.Text = Sheets("Data").Range("CU27").Text
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Maybe something like this
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Amend
    If .Visible = True Then
        .TextBox960.Text = Sheets("Data").Range("CU25").Text
        .TextBox961.Text = Sheets("Data").Range("CU26").Text
        .TextBox962.Text = Sheets("Data").Range("CU27").Text
    End If
End With

With Input
    If .Visible = True Then
        .TextBox170.Text = Sheets("Data").Range("CU25").Text
        .TextBox171.Text = Sheets("Data").Range("CU26").Text
        .TextBox172.Text = Sheets("Data").Range("CU27").Text
    End If
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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