Problem with WorkSheet with multiple workSheet Change Event

zubin

New Member
Joined
Sep 15, 2019
Messages
47
Hi All.....
Making some modifications in my workbook as I have ample time on hand due to lock down.
I have a worksheet change event given below.....i need to replicate the same event three times with different ranges.
Went through alot of threads but could not find one specific to my need....
Please help
VBA Code:
'Quantity input Box Column Default= "1"
Private Sub Worksheet_Change(ByVal Target As Range)
 
 
    Const SCAN_CELL As String = "C2"
    Const RANGE_BC As String = "B4:B14"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 3)
            .Value = .Value + 1
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        
        f.Offset(0, 3).Value = 1
    End If
 Application.EnableEvents = False
    Target.Value = ""
 Application.EnableEvents = True
    Target.Select
 
 End Sub
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const SCAN_CELL As String = "JC2"
    Const RANGE_BC As String = "JB4:JB14"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 3)
            .Value = .Value + 1
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        
        f.Offset(0, 3).Value = 1
    End If
 Application.EnableEvents = False
    Target.Value = ""
 Application.EnableEvents = True
    Target.Select

 
End Sub


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
     Const SCAN_CELL As String = "KC2"
    Const RANGE_BC As String = "KB4:KB14"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 3)
            .Value = .Value + 1
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        
        f.Offset(0, 3).Value = 1
    End If
 Application.EnableEvents = False
    Target.Value = ""
 Application.EnableEvents = True
    Target.Select
 
   End Sub

The above three code perform exactly the same function but with three different ranges.
I need to combine them in the same Worksheet change event.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
VBA Code:
Private Sub UserForm_Initialize() 'frmSaleScan    1st Userform

If ActiveSheet.Range("B4").Value = "" Then
frmSaleScan.ListBox1.Enabled = False
End If

frmSaleScan.ListBox1.RowSource = "SaleScan"
ActiveSheet.Range("C2").Select
frmSaleScan.TextBox2.Value = ""
frmSaleScan.TextBox4.Value = ""
frmSaleScan.CheckBox1.Value = False
frmSaleScan.TextBox2.Enabled = False
frmSaleScan.TextBox4.Enabled = False
frmSaleScan.TextBox7.Enabled = False
frmSaleScan.CommandButton8.Enabled = False
frmSaleScan.TextBox6.Value = ActiveSheet.Range("G23").Value
frmSaleScan.TextBox3.Value = ActiveSheet.Range("D1").Value

frmSaleScan.Label8.Caption = ActiveSheet.Range("C1").Value
frmSaleScan.Label12.Caption = ActiveSheet.Range("B1").Value
frmSaleScan.Label13.Caption = ActiveSheet.Range("F1").Value
frmSaleScan.Label11.Caption = ActiveSheet.Range("E1").Value
ActiveSheet.Range("D21").Value = ActiveSheet.Range("D19").Value - ActiveSheet.Range("D20").Value
'frmSaleScan.TextBox1.SetFocus

With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
    
Me.Height = Me.Height - 10
HideTitleBar.HideTitleBar Me

End Sub

Private Sub UserForm_Initialize()  'frmSaleScan2    2nd Userform

If Sheet1.Range("JB4").Value = "" Then
frmSaleScan2.ListBox1.Enabled = False
End If

frmSaleScan2.ListBox1.RowSource = "SaleScan2"
Sheet1.Range("JC2").Select
frmSaleScan2.TextBox2.Value = ""
frmSaleScan2.TextBox4.Value = ""
frmSaleScan2.CheckBox1.Value = False
frmSaleScan2.TextBox2.Enabled = False
frmSaleScan2.TextBox4.Enabled = False
frmSaleScan2.TextBox7.Enabled = False
frmSaleScan2.CommandButton8.Enabled = False
frmSaleScan2.TextBox6.Value = Sheet1.Range("G23").Value
frmSaleScan2.TextBox3.Value = Sheet1.Range("JD1").Value

frmSaleScan2.Label8.Caption = Sheet1.Range("C1").Value
frmSaleScan2.Label12.Caption = Sheet1.Range("B1").Value
frmSaleScan2.Label13.Caption = Sheet1.Range("F1").Value
frmSaleScan2.Label11.Caption = Sheet1.Range("E1").Value
'Sheet1.Range("D21").Value = Sheet1.Range("D19").Value - Sheet1.Range("D20").Value

With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
    
Me.Height = Me.Height - 10
HideTitleBar.HideTitleBar Me

End Sub

Private Sub UserForm_Initialize()   'frmSaleScan3   3rd Userform

If Sheet1.Range("KB4").Value = "" Then
ListBox1.Enabled = False
End If

frmSaleScan3.ListBox1.RowSource = "SaleScan3"
Sheet1.Range("KC2").Select
frmSaleScan3.TextBox2.Value = ""
frmSaleScan3.TextBox4.Value = ""
frmSaleScan3.CheckBox1.Value = False
frmSaleScan3.TextBox2.Enabled = False
frmSaleScan3.TextBox4.Enabled = False
frmSaleScan3.TextBox7.Enabled = False
frmSaleScan3.CommandButton8.Enabled = False
frmSaleScan3.TextBox6.Value = Sheet1.Range("G23").Value
frmSaleScan3.TextBox3.Value = Sheet1.Range("KD1").Value

frmSaleScan3.Label8.Caption = Sheet1.Range("C1").Value
frmSaleScan3.Label12.Caption = Sheet1.Range("B1").Value
frmSaleScan3.Label13.Caption = Sheet1.Range("F1").Value
frmSaleScan3.Label11.Caption = Sheet1.Range("E1").Value
'Sheet1.Range("D21").Value = Sheet1.Range("D19").Value - Sheet1.Range("D20").Value

With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
    
Me.Height = Me.Height - 10
HideTitleBar.HideTitleBar Me

End Sub



I think i had tried this option of disabling screen updating.....but it had created some other problem....not sure, i don't remember
 
Upvote 0
Where did the ranges "B4:F17" and "JB4:JF17" come from?
 
Upvote 0
Code:
Private Sub ToggleButton1_Click()  ' Toggle button on frmSaleScan
Unload Me
frmSaleScan2.Show
End Sub

Private Sub ToggleButton2_Click()   ' Toggle button on frmSaleScan
Unload Me
frmSaleScan3.Show
End Sub

Private Sub ToggleButton1_Click()    ' Toggle button on frmSaleScan2
Unload Me
frmSaleScan.Show
End Sub

Private Sub ToggleButton2_Click()     ' Toggle button on frmSaleScan2
Unload Me
frmSaleScan3.Show
End Sub
Private Sub ToggleButton1_Click()      ' Toggle button on frmSaleScan3
Unload Me
frmSaleScan.Show
End Sub

Private Sub ToggleButton2_Click()       ' Toggle button on frmSaleScan3
Unload Me
frmSaleScan2.Show
End Sub


And these are the codes for the toggle buttons .....two on each userform to navigate between the three userforms
 
Upvote 0
Where did the ranges "B4:F17" and "JB4:JF17" come from?
These are the ranges i created in a single worksheet which originally were in three different worksheets.....These three ranges are a part of the useform where in the product barcode are scanned during counter sales
 
Upvote 0
These are the ranges i created in a single worksheet which originally were in three different worksheets.....These three ranges are a part of the useform where in the product barcode are scanned during counter sales
The problem i faced after shifting the ranges to a single sheet was the worksheet change event....which you solved ....but as described earlier the the main reason for doing this was the that everytime i navigate between these userforms there is a short spell of time in which the worksheet becomes visible for a fraction of a second before the other userform opens .......This problem persists even after shifting the ranges to a single worksheet.
 
Upvote 0
Try changing the toggle button code to open the new userform before closing the existing one.
 
Upvote 0
Does making the change I suggested work?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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