VBA for Add or Hide Rows Based on Different Sheet Selection

[kkoruni]

Hi all,
I am stuck on a code and need your help. How do I formulate code that if selection on cell#A3 in sheet1 equals "Yes" then display rows 4-34 on sheet2, and if selection on cell#A3 in sheet1 equals "No" then hide rows 4-34 on sheet2.
I was able to get vba to work for the same worksheet. but I am struggling with different worksheet selection. Can you please help? below is the vba code i built thus far.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" Then
If Target = "Yes" Then
AddForms
ElseIf Target = "No" Then
HideForms

End If
End If
End Sub
-------------------------------
Sub AddForms()
Rows("4:34").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Sub
-------------------------------
Sub HideForms()
Rows("4:34").Select
ActiveWindow.SmallScroll Down:=9
Rows("4:34").Select
Selection.EntireRow.Hidden = True
Range("A1").Select
End Sub

 

[Joe4]

Welcome to the Board!

You have to add the Sheet reference.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$3" Then
        If Target = "Yes" Then
            AddForms
        ElseIf Target = "No" Then
            HideForms
        End If
    End If
End Sub

Sub AddForms()
    Sheets("Sheet2").Rows("4:34").Hidden = False
End Sub

Sub HideForms()
    Sheets("Sheet2").Rows("4:34").Hidden = True
End Sub

 

[kkoruni]

Welcome to the Board!

You have to add the Sheet reference.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$3" Then
        If Target = "Yes" Then
            AddForms
        ElseIf Target = "No" Then
            HideForms
        End If
    End If
End Sub

Sub AddForms()
    Sheets("Sheet2").Rows("4:34").Hidden = False
End Sub

Sub HideForms()
    Sheets("Sheet2").Rows("4:34").Hidden = True
End Sub

Joe4,
Thank you for the feedback and welcome. It has been a long time since I chatted on this forum. Glad to be back. I feel I have to get back to some simple basics.
Your guidance absolutely simplified my VBA code. I now see I was missing the basics and was thinking more complicated.

I also, need one more time your help on VBA code.
I do not want one VBA code (Private Sub Worksheet_Change(ByVal Target As Range) to dictate all my 'addforms' or 'hideforms'.
I want add multiple (Private Sub Worksheet_Change(ByVal Target As Range) . How do I accomplish this tasks?

For example:
On sheet3 I have 4 cells that will dictate which rows to hide:
Sheet3 Cell#c4 will hide rows 10-24 in sheet4
Sheet3 Cell#c14 will hide rows 30-44 in sheet4
Sheet3 Cell#c24 will hide rows 50-64in sheet4
Sheet3 Cell#c34 will hide rows 70-84in sheet4

Then for sheet6 I want to add another (Private Sub Worksheet_Change(ByVal Target As Range) dedicated for sheet7 rows. How do I accomplish this task?
On sheet6 I have 4 cells that will dictate which rows to hide:
Sheet6 Cell#c4 will hide rows 10-24 in sheet7
Sheet6 Cell#c14 will hide rows 30-44 in sheet7
Sheet6 Cell#c24 will hide rows 50-64in sheet7
Sheet6 Cell#c34 will hide rows 70-84in sheet7

Let me know if this is possible.

Thanks again,
Kostika

 

[Joe4]

Then for sheet6 I want to add another (Private Sub Worksheet_Change(ByVal Target As Range) dedicated for sheet7 rows. How do I accomplish this task?
On sheet6 I have 4 cells that will dictate which rows to hide:
Sheet6 Cell#c4 will hide rows 10-24 in sheet7
Sheet6 Cell#c14 will hide rows 30-44 in sheet7
Sheet6 Cell#c24 will hide rows 50-64in sheet7
Sheet6 Cell#c34 will hide rows 70-84in sheet7

So each sheet will need its one code. But rather than having 3 separate VBA procedures for each one, you can do it all in one.
Here is what the one for Sheet6 would look like (the others should look very similar, just change the sheet references).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See which cell was updated and set range
    Select Case Target.Address(0, 0)
        Case "C4"
            Set rng = Sheets("Sheet7").Rows("10:24")
        Case "C14"
            Set rng = Sheets("Sheet7").Rows("30:44")
        Case "C24"
            Set rng = Sheets("Sheet7").Rows("50:64")
        Case "C34"
            Set rng = Sheets("Sheet7").Rows("70:84")
    End Select
    
'   Determine whether or not to hide/show rows
    Select Case Target.Value
        Case "Yes"
            rng.Hidden = False
        Case "No"
            rng.Hidden = True
    End Select
    
End Sub

 

[kkoruni]

Thank you Joe4. I appreciate your help.

I am in need of your help with below vba. I am able to export tab with ease. However, I want to be able to export it as .csv file type. Is there a vba code that allows me to export as .csv excel file type? If no, then is there a way to export and automatic save as .csv file type on a desktop folder?

Private Sub CommandButton1_Click()

Sheets("PROF_Data_Capture").Select
ActiveSheet.Unprotect

Sheets("PROF_Data_Capture").Copy
Application.Range("A1:B36").Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CommandBars("Control Toolbox").Visible = True

ActiveSheet.Shapes("CommandButton1").Select
Application.CutCopyMode = False
Selection.Delete
Application.CommandBars("Control Toolbox").Visible = False
Application.Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

[Joe4]

Thank you Joe4. I appreciate your help.

I am in need of your help with below vba. I am able to export tab with ease. However, I want to be able to export it as .csv file type. Is there a vba code that allows me to export as .csv excel file type? If no, then is there a way to export and automatic save as .csv file type on a desktop folder?

Private Sub CommandButton1_Click()

Sheets("PROF_Data_Capture").Select
ActiveSheet.Unprotect

Sheets("PROF_Data_Capture").Copy
Application.Range("A1:B36").Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CommandBars("Control Toolbox").Visible = True

ActiveSheet.Shapes("CommandButton1").Select
Application.CutCopyMode = False
Selection.Delete
Application.CommandBars("Control Toolbox").Visible = False
Application.Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

That is a completely different question that your original one.
As such, you should post it to a new thread, so it appears as a new, unanswered questions and everyone can see it.

General rule of thumb is this:
- Only post back to original thread if new question is about something already posted in the thread, or is a directed-related follow-up questions dependent upon the previous posts.
- If it is a new question (even if on the same project), it is best to post it to a new thread.

 

[kkoruni]

Thank you Joe4.