If A56 is “Yes’, hide Column A58:A60.

  • Thread starter Thread starter Damil4real
  • Start date Start date
You cant hide just a few cells, you have to hide the entire row or column.
You will have to write code to do this automatically, here is the code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
Rows("58:60").Select
Selection.EntireRow.Hidden = True
End If
End Sub
 
Hi
You could cheat a bit by using Conditional Formatting and colouring the font
white if A56 is "Yes"
Reply if you need instructions
HTH
John



If A56 is “Yes’, hide Column A58:A60.

How can I accomplish that?

Thanks!
 
Hi
You could cheat a bit by using Conditional Formatting and colouring the font
white if A56 is "Yes"
Reply if you need instructions
HTH
John


If A56 is “Yes’, hide Column A58:A60.

How can I accomplish that?

Thanks!
Click to expand...

Thanks, John, but seems like I can't click on any other cell once that
code is in effect. it keeps updating itself and won't let me click on
another cell. I'm trying to create a form, and so I have to click on
other cells...etc.

How can I avoid that?

Thanks!
 
This one relies on a CHANGE made in cell B1. Hides if yes, unhides if
anything else

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi
You could cheat a bit by using Conditional Formatting and colouring the
font
white if A56 is "Yes"
Reply if you need instructions
HTH
John


If A56 is “Yes’, hide Column A58:A60.

How can I accomplish that?

Thanks!
Click to expand...

Thanks, John, but seems like I can't click on any other cell once that
code is in effect. it keeps updating itself and won't let me click on
another cell. I'm trying to create a form, and so I have to click on
other cells...etc.

How can I avoid that?

Thanks!
 
This one relies on a CHANGE made in cell B1. Hides if yes, unhides if
anything else

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







Thanks, John, but seems like I can't click on any other cell once that
code is in effect. it keeps updating itself and won't let me click on
another cell. I'm trying to create a form, and so I have to click on
other cells...etc.

How can I avoid that?

Thanks!- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks, Ron! But the code is not working.

I pasted the code in the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
Rows("58:60").Select
Selection.EntireRow.Hidden = True
End If
End Sub

I entered "No" in A56, but nothing is happening.

Thanks!
 
Are you replying to Don or Ron?? If to Don, I said to use THIS, as IS

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$56" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
This one relies on a CHANGE made in cell B1. Hides if yes, unhides if
anything else

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message







Thanks, John, but seems like I can't click on any other cell once that
code is in effect. it keeps updating itself and won't let me click on
another cell. I'm trying to create a form, and so I have to click on
other cells...etc.

How can I avoid that?

Thanks!- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks, Ron! But the code is not working.

I pasted the code in the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
Rows("58:60").Select
Selection.EntireRow.Hidden = True
End If
End Sub

I entered "No" in A56, but nothing is happening.

Thanks!
 
Are you replying to Don or Ron?? If to Don, I said to use THIS, as IS

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$56" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software










Thanks, Ron! But the code is not working.

I pasted the code in the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
  Rows("58:60").Select
    Selection.EntireRow.Hidden = True
    End If
End Sub

I entered "No" in A56, but nothing is happening.

Thanks!
Click to expand...

Code works great! You're a genius, my friend!

Thanks a great deal!
 
Glad you figured it out.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Are you replying to Don or Ron?? If to Don, I said to use THIS, as IS

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$56" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message










Thanks, Ron! But the code is not working.

I pasted the code in the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
Rows("58:60").Select
Selection.EntireRow.Hidden = True
End If
End Sub

I entered "No" in A56, but nothing is happening.

Thanks!
Click to expand...

Code works great! You're a genius, my friend!

Thanks a great deal!
 
Glad you figured it out.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software











Code works great! You're a genius, my friend!

Thanks a great deal!- Hide quoted text -

- Show quoted text -
Click to expand...

One more question, Don. You had supplied me with a code that enables
sheet to hide itself based on cell values. That code is placed on a
sheet named "main." And it's a Private Sub Worksheet_Change code (the
code is pasted below). My question is how can I combine the code below
with the one (hide row code) you just supplied above. The code does
not work when I tried to place both on the sheet named "main."

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If IsNumeric(Target) And Target _
<= Sheets.Count And Target <> 1 Then
For Each WS In Worksheets
If WS.Name <> "Main" Then WS.Visible = False
Next WS
Sheets("Sheet" & Target).Visible = True

End If

End Sub

The hide row code you just provided is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
Rows("58:60").Select
Selection.EntireRow.Hidden = True
End If
End Sub

How can I combine both so they can both work at the same time? The
sheet name is "main" Or how can I make three or four of these Private
Sub Worksheet_SelectionChange work with each other so I can always
adjust it going forward?

Thanks!
 
I, Don Guillett, did NOT provide what you have shown. I provided. What do
you want to do????

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$56" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Glad you figured it out.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message











Code works great! You're a genius, my friend!

Thanks a great deal!- Hide quoted text -

- Show quoted text -
Click to expand...

One more question, Don. You had supplied me with a code that enables
sheet to hide itself based on cell values. That code is placed on a
sheet named "main." And it's a Private Sub Worksheet_Change code (the
code is pasted below). My question is how can I combine the code below
with the one (hide row code) you just supplied above. The code does
not work when I tried to place both on the sheet named "main."

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If IsNumeric(Target) And Target _
<= Sheets.Count And Target <> 1 Then
For Each WS In Worksheets
If WS.Name <> "Main" Then WS.Visible = False
Next WS
Sheets("Sheet" & Target).Visible = True

End If

End Sub

The hide row code you just provided is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
Rows("58:60").Select
Selection.EntireRow.Hidden = True
End If
End Sub

How can I combine both so they can both work at the same time? The
sheet name is "main" Or how can I make three or four of these Private
Sub Worksheet_SelectionChange work with each other so I can always
adjust it going forward?

Thanks!
 
I, Don Guillett, did NOT provide what you have shown. I provided. What do
you want to do????

 Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$A$56" Then Exit Sub
 Rows.Hidden = False
 If UCase(Target) = "YES" Then Rows("58:60").Hidden = True
 End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







One more question, Don. You had supplied me with a code that enables
sheet to hide itself based on cell values. That code is placed on a
sheet named "main." And it's a Private Sub Worksheet_Change code (the
code is pasted below). My question is how can I combine the code below
with the one (hide row code) you just supplied above. The code does
not work when I tried to place both on the sheet named "main."

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If IsNumeric(Target) And Target _
  <= Sheets.Count And Target <> 1 Then
For Each WS In Worksheets
  If WS.Name <> "Main" Then WS.Visible = False
Next WS
Sheets("Sheet" & Target).Visible = True

End If

End Sub

The hide row code you just provided is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A56") = "Yes" Then
  Rows("58:60").Select
    Selection.EntireRow.Hidden = True
    End If
End Sub

How can I combine both so they can both work at the same time? The
sheet name is "main" Or how can I make three or four of these Private
Sub Worksheet_SelectionChange work with each other so I can always
adjust it going forward?

Thanks!- Hide quoted text -

- Show quoted text -
Click to expand...


Ok, Don G.

I have a workbook of about 20 sheets. I have a macro on the sheet
named “main.” When the macro is clicked, it hides all sheets and only
shows one sheet based on the cell value of A1. When you click the
macro, 2 shows up in cell A1, which shows Sheet2 and hides all other
sheets including the sheet named “main.” On Sheet2, there is a macro
when clicked, hides sheet2, and shows sheet main…so that I can click
on another embedded macro that puts 3 in A1, so sheet3 is shown while
all other sheets are hidden…etc.

This is done via the code below (it also includes several other codes
in the module..etc). The code below is in the “main” worksheet, which
is a form that feeds to several forms in the different sheets (I think
from sheets 2 to 30)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If IsNumeric(Target) And Target _
<= Sheets.Count And Target <> 1 Then
For Each WS In Worksheets
If WS.Name <> "Main" Then WS.Visible = False
Next WS
Sheets("Sheet" & Target).Visible = True
End If
End Sub

I’m trying to add the code you provided to the above code, so that
when I’m on sheet “main”, if a cell I65 shows “NO” then hide rows
66:68.

I hope that helps.
Thanks!
 
Back
Top