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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
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,114,584
Messages
5,548,883
Members
410,882
Latest member
ADJ
Top