Problem with WorkSheet with multiple workSheet Change Event

[zubin]

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

'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

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

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.

 

[zubin]

Have you disabled screen updating in the userform codes?

Do you mean in the userform initialize codes ?
I am posting the 3 userform initialize codes......

 

[zubin]

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

 

[Norie]

Where did the ranges "B4:F17" and "JB4:JF17" come from?

 

[zubin]

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

 

[zubin]

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

 

[zubin]

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.

 

[Fluff]

Try changing the toggle button code to open the new userform before closing the existing one.

 

[zubin]

Try changing the toggle button code to open the new userform before closing the existing one.

Or is this problem due to using a toggle button instead of a command button

 

[Fluff]

Does making the change I suggested work?

 

[zubin]

Does making the change I suggested work?

No I haven't tried yet.....I'll get back very soon