Banno
04-24-2020, 08:05 AM
Hello Everyone! hope all are safe & healthy!!
Need to ask for some help here <--- Beginner
So here is what is happening,
I have a master sheet it gets data from another sheet for drop down lists
the program also hides and reveals cells as you select the info in the drop downs.
the program works fine on the master sheet until I copy it to another sheet then it doesn't seem to work at all
or as you change the master the other sheet changes as well
Can you guys give me some clarity and point me down the right track please as to what i am doing wrong here?
Here is a copy of the code I'm am working with it is not completed I'm just wondering why it is working on both sheets and not the active one?
Thanks for all your help!
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B5"), Range(Target.Address)) Is Nothing Then
For a = 7 To 13
Worksheets(1).Rows(a).Hidden = True
Next
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = True
Next
For a = 18 To 24
Worksheets(1).Rows(a).Hidden = True
Next
For a = 25 To 33
Worksheets(1).Rows(a).Hidden = True
Next
For a = 34 To 42
Worksheets(1).Rows(a).Hidden = True
Next
For a = 43 To 51
Worksheets(1).Rows(a).Hidden = True
Next
For a = 52 To 60
Worksheets(1).Rows(a).Hidden = True
Next
For a = 62 To 79
Worksheets(1).Rows(a).Hidden = True
Next
For a = 80 To 104
Worksheets(1).Rows(a).Hidden = True
Next
For a = 105 To 107
Worksheets(1).Rows(a).Hidden = True
Next
If Cells(5, 2).Value = "Investigation (ICAR)" Then
CB3.Visible = False
CB4.Visible = False
CB5.Visible = False
MsgBox "Please select 'Out of Box Failure' or 'Nonconformity' Thank you"
cb1.Visible = True
CB2.Visible = True
Else
If Cells(5, 2).Value = "Customer Encounter (CCAR)" Then
cb1.Visible = False
CB2.Visible = False
MsgBox "Please select 'Complaint' or 'Incident' or 'Compliment' Thank you"
CB3.Visible = True
CB4.Visible = True
CB5.Visible = True
Else
If Cells(5, 2).Value = "Audit Corrective Action (ACAR)" Then
For a = 7 To 13
Worksheets(1).Rows(a).Hidden = False
Next
For a = 62 To 107
Worksheets(1).Rows(a).Hidden = False
Next
Else
If Cells(5, 2).Value = "N/A" Then
For a = 7 To 13
Worksheets(1).Rows(a).Hidden = True
Next
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = True
Next
For a = 18 To 24
Worksheets(1).Rows(a).Hidden = True
Next
For a = 25 To 33
Worksheets(1).Rows(a).Hidden = True
Next
For a = 34 To 42
Worksheets(1).Rows(a).Hidden = True
Next
For a = 43 To 51
Worksheets(1).Rows(a).Hidden = True
Next
For a = 52 To 60
Worksheets(1).Rows(a).Hidden = True
Next
For a = 62 To 79
Worksheets(1).Rows(a).Hidden = True
Next
For a = 80 To 104
Worksheets(1).Rows(a).Hidden = True
Next
For a = 105 To 107
Worksheets(1).Rows(a).Hidden = True
Next
cb1.Visible = False
CB2.Visible = False
CB3.Visible = False
CB4.Visible = False
CB5.Visible = False
Else
cb1.Visible = False
CB2.Visible = False
CB3.Visible = False
CB4.Visible = False
CB5.Visible = False
End If
End If
End If
End If
End If
If Not Application.Intersect(Range("B13"), Range(Target.Address)) Is Nothing Then
If Cells(13, 2).Value = "No" Then
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = True
Next
Else
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = False
Next
End If
End If
End Sub
Private Sub CB1_Click()
'Out of Box Failure'
CB2.Visible = False
For a = 19 To 25
Worksheets(1).Rows(a).Hidden = False
Next
End Sub
Private Sub CB2_Click()
'Nonconformity'
For a = 26 To 33
Worksheets(1).Rows(a).Hidden = True
Next
cb1.Visible = False
End Sub
Private Sub CB3_Click()
'Complaint'
'Incident'
CB4.Visible = False
CB5.Visible = False
End Sub
Private Sub CB4_Click()
'Incident'
CB3.Visible = False
CB5.Visible = False
End Sub
Private Sub CB5_Click()
'Compliment'
CB3.Visible = False
CB4.Visible = False
End Sub
Private Sub CB6_Click()
UserForm1.Show
End Sub
Need to ask for some help here <--- Beginner
So here is what is happening,
I have a master sheet it gets data from another sheet for drop down lists
the program also hides and reveals cells as you select the info in the drop downs.
the program works fine on the master sheet until I copy it to another sheet then it doesn't seem to work at all
or as you change the master the other sheet changes as well
Can you guys give me some clarity and point me down the right track please as to what i am doing wrong here?
Here is a copy of the code I'm am working with it is not completed I'm just wondering why it is working on both sheets and not the active one?
Thanks for all your help!
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B5"), Range(Target.Address)) Is Nothing Then
For a = 7 To 13
Worksheets(1).Rows(a).Hidden = True
Next
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = True
Next
For a = 18 To 24
Worksheets(1).Rows(a).Hidden = True
Next
For a = 25 To 33
Worksheets(1).Rows(a).Hidden = True
Next
For a = 34 To 42
Worksheets(1).Rows(a).Hidden = True
Next
For a = 43 To 51
Worksheets(1).Rows(a).Hidden = True
Next
For a = 52 To 60
Worksheets(1).Rows(a).Hidden = True
Next
For a = 62 To 79
Worksheets(1).Rows(a).Hidden = True
Next
For a = 80 To 104
Worksheets(1).Rows(a).Hidden = True
Next
For a = 105 To 107
Worksheets(1).Rows(a).Hidden = True
Next
If Cells(5, 2).Value = "Investigation (ICAR)" Then
CB3.Visible = False
CB4.Visible = False
CB5.Visible = False
MsgBox "Please select 'Out of Box Failure' or 'Nonconformity' Thank you"
cb1.Visible = True
CB2.Visible = True
Else
If Cells(5, 2).Value = "Customer Encounter (CCAR)" Then
cb1.Visible = False
CB2.Visible = False
MsgBox "Please select 'Complaint' or 'Incident' or 'Compliment' Thank you"
CB3.Visible = True
CB4.Visible = True
CB5.Visible = True
Else
If Cells(5, 2).Value = "Audit Corrective Action (ACAR)" Then
For a = 7 To 13
Worksheets(1).Rows(a).Hidden = False
Next
For a = 62 To 107
Worksheets(1).Rows(a).Hidden = False
Next
Else
If Cells(5, 2).Value = "N/A" Then
For a = 7 To 13
Worksheets(1).Rows(a).Hidden = True
Next
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = True
Next
For a = 18 To 24
Worksheets(1).Rows(a).Hidden = True
Next
For a = 25 To 33
Worksheets(1).Rows(a).Hidden = True
Next
For a = 34 To 42
Worksheets(1).Rows(a).Hidden = True
Next
For a = 43 To 51
Worksheets(1).Rows(a).Hidden = True
Next
For a = 52 To 60
Worksheets(1).Rows(a).Hidden = True
Next
For a = 62 To 79
Worksheets(1).Rows(a).Hidden = True
Next
For a = 80 To 104
Worksheets(1).Rows(a).Hidden = True
Next
For a = 105 To 107
Worksheets(1).Rows(a).Hidden = True
Next
cb1.Visible = False
CB2.Visible = False
CB3.Visible = False
CB4.Visible = False
CB5.Visible = False
Else
cb1.Visible = False
CB2.Visible = False
CB3.Visible = False
CB4.Visible = False
CB5.Visible = False
End If
End If
End If
End If
End If
If Not Application.Intersect(Range("B13"), Range(Target.Address)) Is Nothing Then
If Cells(13, 2).Value = "No" Then
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = True
Next
Else
For a = 14 To 17
Worksheets(1).Rows(a).Hidden = False
Next
End If
End If
End Sub
Private Sub CB1_Click()
'Out of Box Failure'
CB2.Visible = False
For a = 19 To 25
Worksheets(1).Rows(a).Hidden = False
Next
End Sub
Private Sub CB2_Click()
'Nonconformity'
For a = 26 To 33
Worksheets(1).Rows(a).Hidden = True
Next
cb1.Visible = False
End Sub
Private Sub CB3_Click()
'Complaint'
'Incident'
CB4.Visible = False
CB5.Visible = False
End Sub
Private Sub CB4_Click()
'Incident'
CB3.Visible = False
CB5.Visible = False
End Sub
Private Sub CB5_Click()
'Compliment'
CB3.Visible = False
CB4.Visible = False
End Sub
Private Sub CB6_Click()
UserForm1.Show
End Sub