Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Hide a row using Visual Basic
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5
Default 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.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5
Default 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.




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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.





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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.





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5
Default 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.






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visual Basic osaka78 Excel Discussion (Misc queries) 5 September 17th 07 01:21 PM
Visual Basic Co-op 20 Excel Discussion (Misc queries) 2 June 28th 07 09:06 PM
changing the visual basic in office 2003 to visual studio net bigdaddy3 Excel Discussion (Misc queries) 1 September 13th 05 10:57 AM
Visual Basic Micos3 Excel Discussion (Misc queries) 9 June 28th 05 01:41 PM
Visual Basic Help Duncan Smith Excel Discussion (Misc queries) 1 December 3rd 04 09:13 AM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement