Hide a row using Visual Basic

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I am trying to create a formula that if certain names (Package3 and
Package4) are picked from a cell with data validation, a goup of cells become
visible. If nothing, Package1 or Package2 are picked, the cells remain
hidden.

Thank you for your help.
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
If .Value = "Package3" Or .Value = "Package4" Then
Me.Range("3:10").EntireRow.Hidden = False
End If
End With
stoppit:
Application.EnableEvents = True
End

This is sheet event code. Right-click on sheet tab and "View Code"

Copy/paste into that sheet module.

Adjust A1 and 3:10 to suit


Gord Dibben MS Excel MVP
 
Hi Gord. Thank you for your reply. I am getting a "Compile Error: Expected
End Sub"

I replaced "Package3" and "Package4" with "Cutting Edge Plus" and "Ultimate"
as those are the actual names.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("D6")
If .Value = "Cutting Edge Plus" Or .Value = "Ultimate" Then
Me.Range("J11:J21").EntireRow.Hidden = False
End If
End With
stoppit:
Application.EnableEvents = True
End

Thank you,

Steve
 
Add the End Sub line after the End line in the code.

Application.EnableEvents = True
End
End Sub

Got lost some place along the road<g>


Gord
 
well...The error message is now gone but nothing is happening. I now have...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("D6")
If .Value = "Cutting Edge Plus" Or .Value = "Ultimate" Then
Me.Range("J11:J21").EntireRow.Hidden = False
End If
End With
stoppit:
Application.EnableEvents = True
End
End Sub

Is it somehting with "J11:J21" as I am trying to conceal only those cells
and I see the command "Entirerow.Hidden"

Thank you!
 
After re-reading your original post, I see my error.

You wanted to "conceal" a range of cells. I took it to mean hide rows.

You cannot hide just a range of cells like J11:J21

You could change the font color to make the cells look blank.

That can be done simply through Conditional Formatting without the need for
event code.

Select J1:J21 and Format>Conditional Formatting>Formula is:

=OR($D$6="Cutting Edge Plus",$D$6="Ultimate")

Format the font to white.


Gord
 
IF you wanted to use event code instead of the easier Conditional Formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("D6")
If .Value = "Cutting Edge Plus" Or .Value = "Ultimate" Then
Me.Range("J11:J21").Font.ColorIndex = 2
End If
End With
stoppit:
Application.EnableEvents = True
End
End Sub


Gord
 
Thanks!

Gord Dibben said:
IF you wanted to use event code instead of the easier Conditional Formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("D6")
If .Value = "Cutting Edge Plus" Or .Value = "Ultimate" Then
Me.Range("J11:J21").Font.ColorIndex = 2
End If
End With
stoppit:
Application.EnableEvents = True
End
End Sub


Gord
Click to expand...
 
Back
Top