Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
I appreciate any help on this. I am new to this and just trying to figure it
out. Enclosed is what I have. The issue I am having is that everytime I change anything in the spreadsheet it runs through this the entire program and the sheet flashes etc. What I would like to do if possible is have the program after "'Section 7 hide rows depending on the outcome" and to run automatically if ONLY Cell L62 Changes. If L62 does not change then then skip the process. Is this possible? If I am not making any sense let me know. Private Sub Worksheet_Change(ByVal Target As Range) If UCase(Range("B10")) = "X" Or UCase(Range("B11")) = "X" Then Rows(14 & ":" & 19).EntireRow.Hidden = False ElseIf WorksheetFunction.CountA(Range("B10:B11")) = 0 Then Rows(14 & ":" & 19).EntireRow.Hidden = True End If If UCase(Range("B24")) = "X" Then Rows(25 & ":" & 29).EntireRow.Hidden = False ElseIf WorksheetFunction.CountA(Range("B24")) = 0 Then Rows(25 & ":" & 29).EntireRow.Hidden = True End If 'Section 7 hide rows depending on the outcome Range("63:147").EntireRow.Hidden = True If Range("L62").Value = 1 Then Range("63:69").EntireRow.Hidden = False ElseIf Range("L62").Value = 2 Then Range("63:75").EntireRow.Hidden = False ElseIf Range("L62").Value = 3 Then Range("63:81").EntireRow.Hidden = False ElseIf Range("L62").Value = 4 Then Range("63:87").EntireRow.Hidden = False ElseIf Range("L62").Value = 5 Then Range("63:93").EntireRow.Hidden = False ElseIf Range("L62").Value = 6 Then Range("63:99").EntireRow.Hidden = False ElseIf Range("L62").Value = 7 Then Range("63:105").EntireRow.Hidden = False ElseIf Range("L62").Value = 8 Then Range("63:111").EntireRow.Hidden = False ElseIf Range("L62").Value = 9 Then Range("63:117").EntireRow.Hidden = False ElseIf Range("L62").Value = 10 Then Range("63:123").EntireRow.Hidden = False ElseIf Range("L62").Value = 11 Then Range("63:129").EntireRow.Hidden = False ElseIf Range("L62").Value = 12 Then Range("63:135").EntireRow.Hidden = False ElseIf Range("L62").Value = 13 Then Range("63:141").EntireRow.Hidden = False ElseIf Range("L62").Value = 14 Then Range("63:147").EntireRow.Hidden = False End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
Hi,
Add this line to your code next to your comment line If Target.Address < "$L$62" Then Exit Sub 'Section 7 hide rows depending on the outcome Mike "Leiprecht" wrote: I appreciate any help on this. I am new to this and just trying to figure it out. Enclosed is what I have. The issue I am having is that everytime I change anything in the spreadsheet it runs through this the entire program and the sheet flashes etc. What I would like to do if possible is have the program after "'Section 7 hide rows depending on the outcome" and to run automatically if ONLY Cell L62 Changes. If L62 does not change then then skip the process. Is this possible? If I am not making any sense let me know. Private Sub Worksheet_Change(ByVal Target As Range) If UCase(Range("B10")) = "X" Or UCase(Range("B11")) = "X" Then Rows(14 & ":" & 19).EntireRow.Hidden = False ElseIf WorksheetFunction.CountA(Range("B10:B11")) = 0 Then Rows(14 & ":" & 19).EntireRow.Hidden = True End If If UCase(Range("B24")) = "X" Then Rows(25 & ":" & 29).EntireRow.Hidden = False ElseIf WorksheetFunction.CountA(Range("B24")) = 0 Then Rows(25 & ":" & 29).EntireRow.Hidden = True End If 'Section 7 hide rows depending on the outcome Range("63:147").EntireRow.Hidden = True If Range("L62").Value = 1 Then Range("63:69").EntireRow.Hidden = False ElseIf Range("L62").Value = 2 Then Range("63:75").EntireRow.Hidden = False ElseIf Range("L62").Value = 3 Then Range("63:81").EntireRow.Hidden = False ElseIf Range("L62").Value = 4 Then Range("63:87").EntireRow.Hidden = False ElseIf Range("L62").Value = 5 Then Range("63:93").EntireRow.Hidden = False ElseIf Range("L62").Value = 6 Then Range("63:99").EntireRow.Hidden = False ElseIf Range("L62").Value = 7 Then Range("63:105").EntireRow.Hidden = False ElseIf Range("L62").Value = 8 Then Range("63:111").EntireRow.Hidden = False ElseIf Range("L62").Value = 9 Then Range("63:117").EntireRow.Hidden = False ElseIf Range("L62").Value = 10 Then Range("63:123").EntireRow.Hidden = False ElseIf Range("L62").Value = 11 Then Range("63:129").EntireRow.Hidden = False ElseIf Range("L62").Value = 12 Then Range("63:135").EntireRow.Hidden = False ElseIf Range("L62").Value = 13 Then Range("63:141").EntireRow.Hidden = False ElseIf Range("L62").Value = 14 Then Range("63:147").EntireRow.Hidden = False End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
That would work, however, I have similar code for other cells too. So I do
not want it to end the whole thing. Cells L62, L159 and E198 How would I incorporate that? "Mike H" wrote: Hi, Add this line to your code next to your comment line If Target.Address < "$L$62" Then Exit Sub 'Section 7 hide rows depending on the outcome Mike "Leiprecht" wrote: I appreciate any help on this. I am new to this and just trying to figure it out. Enclosed is what I have. The issue I am having is that everytime I change anything in the spreadsheet it runs through this the entire program and the sheet flashes etc. What I would like to do if possible is have the program after "'Section 7 hide rows depending on the outcome" and to run automatically if ONLY Cell L62 Changes. If L62 does not change then then skip the process. Is this possible? If I am not making any sense let me know. Private Sub Worksheet_Change(ByVal Target As Range) If UCase(Range("B10")) = "X" Or UCase(Range("B11")) = "X" Then Rows(14 & ":" & 19).EntireRow.Hidden = False ElseIf WorksheetFunction.CountA(Range("B10:B11")) = 0 Then Rows(14 & ":" & 19).EntireRow.Hidden = True End If If UCase(Range("B24")) = "X" Then Rows(25 & ":" & 29).EntireRow.Hidden = False ElseIf WorksheetFunction.CountA(Range("B24")) = 0 Then Rows(25 & ":" & 29).EntireRow.Hidden = True End If 'Section 7 hide rows depending on the outcome Range("63:147").EntireRow.Hidden = True If Range("L62").Value = 1 Then Range("63:69").EntireRow.Hidden = False ElseIf Range("L62").Value = 2 Then Range("63:75").EntireRow.Hidden = False ElseIf Range("L62").Value = 3 Then Range("63:81").EntireRow.Hidden = False ElseIf Range("L62").Value = 4 Then Range("63:87").EntireRow.Hidden = False ElseIf Range("L62").Value = 5 Then Range("63:93").EntireRow.Hidden = False ElseIf Range("L62").Value = 6 Then Range("63:99").EntireRow.Hidden = False ElseIf Range("L62").Value = 7 Then Range("63:105").EntireRow.Hidden = False ElseIf Range("L62").Value = 8 Then Range("63:111").EntireRow.Hidden = False ElseIf Range("L62").Value = 9 Then Range("63:117").EntireRow.Hidden = False ElseIf Range("L62").Value = 10 Then Range("63:123").EntireRow.Hidden = False ElseIf Range("L62").Value = 11 Then Range("63:129").EntireRow.Hidden = False ElseIf Range("L62").Value = 12 Then Range("63:135").EntireRow.Hidden = False ElseIf Range("L62").Value = 13 Then Range("63:141").EntireRow.Hidden = False ElseIf Range("L62").Value = 14 Then Range("63:147").EntireRow.Hidden = False End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |