ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Hide a row using Visual Basic (https://www.excelbanter.com/excel-worksheet-functions/162221-hide-row-using-visual-basic.html)

Steve_Dallas[_2_]
Hide a row using Visual Basic
 
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.

Gord Dibben
Hide a row using Visual Basic
 
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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.

Steve_Dallas[_2_]
Hide a row using Visual Basic
 
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

"Gord Dibben" wrote:

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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.

Gord Dibben
Hide a row using Visual Basic
 
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

On Tue, 16 Oct 2007 12:17:01 -0700, Steve_Dallas
wrote:

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

"Gord Dibben" wrote:

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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.

Steve_Dallas[_2_]
Hide a row using Visual Basic
 
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!


"Gord Dibben" wrote:

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

On Tue, 16 Oct 2007 12:17:01 -0700, Steve_Dallas
wrote:

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

"Gord Dibben" wrote:

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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.

Gord Dibben
Hide a row using Visual Basic
 
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 FormatConditional FormattingFormula is:

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

Format the font to white.


Gord


On Tue, 16 Oct 2007 15:17:00 -0700, Steve_Dallas
wrote:

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!


"Gord Dibben" wrote:

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

On Tue, 16 Oct 2007 12:17:01 -0700, Steve_Dallas
wrote:

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

"Gord Dibben" wrote:

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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.

Gord Dibben
Hide a row using Visual Basic
 
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

On Tue, 16 Oct 2007 15:49:31 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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 FormatConditional FormattingFormula is:

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

Format the font to white.


Gord


On Tue, 16 Oct 2007 15:17:00 -0700, Steve_Dallas
wrote:

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!


"Gord Dibben" wrote:

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

On Tue, 16 Oct 2007 12:17:01 -0700, Steve_Dallas
wrote:

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

"Gord Dibben" wrote:

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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.

Steve_Dallas[_2_]
Hide a row using Visual Basic
 
Thanks!

"Gord Dibben" wrote:

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

On Tue, 16 Oct 2007 15:49:31 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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 FormatConditional FormattingFormula is:

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

Format the font to white.


Gord


On Tue, 16 Oct 2007 15:17:00 -0700, Steve_Dallas
wrote:

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!


"Gord Dibben" wrote:

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

On Tue, 16 Oct 2007 12:17:01 -0700, Steve_Dallas
wrote:

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

"Gord Dibben" wrote:

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


On Mon, 15 Oct 2007 13:52:01 -0700, Steve_Dallas
wrote:

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.


All times are GMT +1. The time now is 01:46 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com