Auto Hide/Unhide Formatting

  • Thread starter Thread starter corey.helms
  • Start date Start date
C

corey.helms

I'm wanting to create conditional formatting such that when users
submit info in one cell, a previously hidden row underneath it becomes
unhidden and available for formatting. Is there a way to do that?
 
Not using Conditional Formatting.

You would need event code to achieve the unhiding of a row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
If Target.Value <> "" Then
Rows("2:2").EntireRow.Hidden = False
End If
endit:
Application.EnableEvents = True
End Sub

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

Paste the code into that module. Edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP
 
Not using Conditional Formatting.

You would need event code to achieve the unhiding of a row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
     Application.EnableEvents = False
If Target.Value <> "" Then
Rows("2:2").EntireRow.Hidden = False
End If
endit:
   Application.EnableEvents = True
End Sub

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

Paste the code into that module.  Edit to suit then Alt + q to return to the
Excel window.

Gord Dibben  MS Excel MVP
Click to expand...

I'm relatively new to VBA, what exactly should I be looking to edit?
 
I used A1 as the input cell and row 2 as the row to unhide.

You would have to edit those to suit.

Would you be wanting this for more than a single cell or single row?

Post some details.......cell references and row numbers.


Gord
 
I used A1 as the input cell and row 2 as the row to unhide.

You would have to edit those to suit.

Would you be wanting this for more than a single cell or single row?

Post some details.......cell references and row numbers.

Gord






- Show quoted text -
Click to expand...

Essentially, I'm creating a sheet that contains three categories of
employees. Under each of these categories, I have as many rows as
there are people in that particular category. By default, no
employees names are chosen. The first column contains data validation
lists containing all employees in each category. What I'm wanting to
have happen is when a user chooses someone from the list on the first
row, the next row becomes available for selection. I'd like for it to
continue down the list, until it reaches the end of each category.

For instance, B4 is the first blank cell. When an employee is chosen
for cell B4, I want row 5 to become available. When B5 is chosen, row
6 opens, etc. Each category ideally would only contain 45-50 names.

Thanks again for all the help!
 
If the DV dropdowns are in column A, how does an employee get chosen for B4
which is blank?

Did you mean the dropdowns are in column B? Or you want code to also enter
in B4 what was chosen from A4 dropdown?

Define "first row" and "first column".

When describing your needs it is always better to use actual references
rather than words like "first" and "next"

What you want should be doable but please clear up the cell and column
references.


Gord
 
If the DV dropdowns are in column A, how does an employee get chosen for B4
which is blank?

Did you mean the dropdowns are in column B?  Or you want code to also enter
in B4 what was chosen from A4 dropdown?

Define "first row" and "first column".

When describing your needs it is always better to use actual references
rather than words like "first" and "next"

What you want should be doable but please clear up the cell and column
references.

Gord







- Show quoted text -
Click to expand...

Employee names will appear in column B.
 
Assuming rows 5 to 101 are hidden.

This will unhide one at a time as you choose a name from column B starting
with B4. Is that what you need?

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100" 'edit to suit
Dim cell As Range
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(1, 0).EntireRow.Hidden = False
End If
End If
endit:
Application.EnableEvents = True
End Sub


Gord
 
Back
Top