If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
how to hide a number of cells based on one cell?
Try the Private Sub Worksheet_Calculate() event instead.
Gord Dibben MS Excel MVP On Thu, 25 Jun 2009 16:28:01 -0700, TG wrote: Gary, How would I do this assuming that I am using a formula? for example I want to use a check box and this check box gives me a true or false answer, I then want to use that True or False answer but on another different spreadsheet in the same way the "no" answer works. As of right now I cannot get the False or True answer to work in my Micro, when I link my macro to lets say A1 (A1 being the cell the TRUE or FALSE from the check box is linked to) it does not work, it only works if I type it in! Got any suggestions?? Thanks Gary, TG "Gary''s Student" wrote: Let's assume that you are setting A1 thru typing (rather than a formula). Enter the folowing event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set a = Range("A1") If Intersect(t, a) Is Nothing Then Exit Sub Application.EnableEvents = False If a.Value = "no" Then Range("A4:A15").EntireRow.Hidden = True Else Range("A4:A15").EntireRow.Hidden = False End If Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200858 "TG" wrote: Hello, How would I be able to hide lets say row 4-15 if A1 had the word no? Is this even possible? Also, what if i also want to hide rows 20-100 if A19 says "no"? Thank you in advance, TG |
#12
|
|||
|
|||
how to hide a number of cells based on one cell?
thanks,
ive tried this but it slows down excel significantly and sometimes it freezes excel. "Gord Dibben" wrote: Try the Private Sub Worksheet_Calculate() event instead. Gord Dibben MS Excel MVP On Thu, 25 Jun 2009 16:28:01 -0700, TG wrote: Gary, How would I do this assuming that I am using a formula? for example I want to use a check box and this check box gives me a true or false answer, I then want to use that True or False answer but on another different spreadsheet in the same way the "no" answer works. As of right now I cannot get the False or True answer to work in my Micro, when I link my macro to lets say A1 (A1 being the cell the TRUE or FALSE from the check box is linked to) it does not work, it only works if I type it in! Got any suggestions?? Thanks Gary, TG "Gary''s Student" wrote: Let's assume that you are setting A1 thru typing (rather than a formula). Enter the folowing event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set a = Range("A1") If Intersect(t, a) Is Nothing Then Exit Sub Application.EnableEvents = False If a.Value = "no" Then Range("A4:A15").EntireRow.Hidden = True Else Range("A4:A15").EntireRow.Hidden = False End If Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200858 "TG" wrote: Hello, How would I be able to hide lets say row 4-15 if A1 had the word no? Is this even possible? Also, what if i also want to hide rows 20-100 if A19 says "no"? Thank you in advance, TG |
|
Thread Tools | |
Display Modes | |
|
|