PDA

View Full Version : code running on more than 1 sheet


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
SamT
04-24-2020, 06:35 PM
Please edit your post to remove all unnecessary double, triple, and quadruple line feeds

Add some endentaion, and use the # button to insert CodeTags around subs and your post can look like

Private Sub CB5_Click()
'Compliment'
CB3.Visible = False
CB4.Visible = False
End Sub



Private Sub CB6_Click()
UserForm1.Show
End Sub
paulked
04-24-2020, 08:06 PM
Hi and welcome to the forum.

To make code more readable it is better to indent, as I have done for you.

I have removed all the references to Worksheets(1) so it should work on any sheet if you put it in its' module:



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
Rows(a).Hidden = True
Next
For a = 14 To 17
Rows(a).Hidden = True
Next
For a = 18 To 24
Rows(a).Hidden = True
Next
For a = 25 To 33
Rows(a).Hidden = True
Next
For a = 34 To 42
Rows(a).Hidden = True
Next
For a = 43 To 51
Rows(a).Hidden = True
Next
For a = 52 To 60
Rows(a).Hidden = True
Next
For a = 62 To 79
Rows(a).Hidden = True
Next
For a = 80 To 104
Rows(a).Hidden = True
Next
For a = 105 To 107
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
Rows(a).Hidden = False
Next
For a = 62 To 107
Rows(a).Hidden = False
Next
Else
If Cells(5, 2).Value = "N/A" Then
For a = 7 To 13
Rows(a).Hidden = True
Next
For a = 14 To 17
Rows(a).Hidden = True
Next
For a = 18 To 24
Rows(a).Hidden = True
Next
For a = 25 To 33
Rows(a).Hidden = True
Next
For a = 34 To 42
Rows(a).Hidden = True
Next
For a = 43 To 51
Rows(a).Hidden = True
Next
For a = 52 To 60
Rows(a).Hidden = True
Next
For a = 62 To 79
Rows(a).Hidden = True
Next
For a = 80 To 104
Rows(a).Hidden = True
Next
For a = 105 To 107
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
Rows(a).Hidden = True
Next
Else
For a = 14 To 17
Rows(a).Hidden = False
Next
End If
End If
End Sub