Worksheet_Change affecting data

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
14
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,935
Messages
5,545,099
Members
410,656
Latest member
Hydraulics
Top