Hide row code help

I am using the following script to hide or unhide rows.  How do I code the 
offset to only work when a row is being unhid.  If we cant do this only 
offset when it is being hidden or unhidden?

Thank you

Private Sub Worksheet_Change(ByVal Target As Range)
  'Application.EnableEvents = False' not necessary
If Not Application.Intersect(Target, Range("B9")) Is Nothing Then
Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling
    ActiveCell.Offset(-6, 0).Select
End If
  'Application.EnableEvents = False
 
End Sub

0
Utf
1/5/2010 3:57:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
928 Views

Similar Articles

[PageSpeed] 5

Hiding/unhiding rows is not a trappable event.

Not clear on what and when you want to occur.


Gord Dibben  MS Excel MVP

On Tue, 5 Jan 2010 07:57:01 -0800, Jeremy <Jeremy@discussions.microsoft.com>
wrote:

>I am using the following script to hide or unhide rows.  How do I code the 
>offset to only work when a row is being unhid.  If we cant do this only 
>offset when it is being hidden or unhidden?
>
>Thank you
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>  'Application.EnableEvents = False' not necessary
>If Not Application.Intersect(Target, Range("B9")) Is Nothing Then
>Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling
>    ActiveCell.Offset(-6, 0).Select
>End If
>  'Application.EnableEvents = False
> 
>End Sub

0
Gord
1/5/2010 6:23:47 PM
Reply:

Similar Artilces:

SPAM Blocking Help
Hi all, We are looking at buying spam blocking software. I would prefer a software solution that does not require changing our MX record. Are there any products you can recommend? Thanks for your input..... I like GFI Mail Essentials at www.gfi.com with newly released Bayesian Analysis or ORF Filter from www.vamsoft.com . Here is link of products http://www.msexchange.org/software/Anti_Spam/ -- John Oliver, Jr. MCSE, MCT, CCNA, Exchange MVP Microsoft Certified Partner "Bob Dickens" <bdickens@-no-spam-shastacollege.edu> wrote in message news:OegzaNl%23DHA.220@TK2MSFTNGP09....

XPath Query Help
I'm trying to write a query to pull all the specific fields from a xml file. The XML is structured in such a way that nested child elements in a section will determine if a field exists in a form. In order to show all the fields in an xml file I need to get all XML elements between two elements. Here is an example: <root> <container> <elem><tag="start" /></elem> <elem><val>Keep this one</val></elem> <elem><tag="stop" /></elem> <elem><val>Discard Me</val></e...

Help formatting a workbook into a webpage
Hello, I have a workbook that I would like to publish to my website. When I do a file/save as webpage the images are way too large for my browser. Does anyone know if there is a way to "reformatt" the sheets to save in a smaller size? Thanks in advance. ...

Please help....
My screen resolution will not save...no matter how many times I change and apply. The screen will first appear normal...than switch to huge icons/fonts. Also, at times, my screen will go totally black...then come back up..sometimes in the larger fonts again and then switch back to normal. But, everytime I turn my computer off, and bring it back up, I'm back to square one again. I've tried everything and nothing helps...it's really getting me down. Thanks for any help you can give me! "Sally" <Sally@discussions.microsoft.com> wrote in message news:D8...

Command button code problem
I have a form with a command button that opens a report in preview. The form has a combo box from which you select the record that should be shown on the report but when I click on the command button I get all the records not just the one I have clicked What's wrong with my code? Here it is Private Sub cmdprintcr_Click() On Error GoTo Err_cmdprintcr_Click Dim strAVCISCode As String 'set strAVCISCode equal to the selected value before closing the form, otherwise it will give us an error strAVCISCode = "AVCISCode" DoCmd.OpenReport "rptCrimereport",...

locking a row?
Running Excel 97 SR-1 and have forgotten what you call locking a row in a sheet so that it stays in view and you can see the names of each column. How do you do it also? Thank you, Steve T Steve T wrote: > Running Excel 97 SR-1 and have forgotten what you call locking a row in a > sheet so that it stays in view and you can see the names of each column. How > do you do it also? Thank you, Steve T Steve -- 1st position your cursor on the row below the one with the titles that you want to show. Next, go to the Window on your Menu Bar and select "Freeze Frames". That s...

How do I return to the start of the next row
I am typing in a row of 8 numbers in EXCEL...when I finish and hit enter, I want to the cursor to move back to the FIRST column of the next row??? How can I command the machine to do this? Thanks Hi one way: - select your entry range before entering your data - now ENTER should bring you to the next row -- Regards Frank Kabel Frankfurt, Germany "Barbara Retzko" <Barbara Retzko@discussions.microsoft.com> schrieb im Newsbeitrag news:0023940B-9946-4E73-9772-10198D728A4E@microsoft.com... > I am typing in a row of 8 numbers in EXCEL...when I finish and hit enter, I >...

Pivot Table Help
I have a column for the actual date and a column for a grade. I hav created a pivot table to total up information in regards to this data. I need to sum up the amounts by the month not the individual day. However I need to keep the individual date as mm/dd/yy. Is there a way that I can manipulate the pivot table to sum up based o the month only? Date 1/2/04 1/3/04 ... 5/6/04 Grade 2 1 ... 5 The pivot table works ok however I really only need to sum up th totals for each month not for each particular day. Is there a way tha I can use the same day column but only calculate the totals...

CListCtrl Changing the row height
Hello, I'm trying to change the the row height of a CListCtrl ( not the total length of the listCtrl) i.e. I want the text to be displayed with the same font size but the height of this text (item) should be increased. This way on a touch screen the distance between the items is large enough to select an item in the ClistCtrl. Currently the clistctrl has one column, and each row data has a small icon and text beside it. Do you have any suggestions. Thanks, Regards, Suresh I think the only way to do it is to use a custom draw list control. This article should explain how to do...

FRx to pull AA Numeric Code
FRx should have the ability to pull/report AA NUMERIC Dim Code. This will add great analytical functionality to the product, I can't understand why this feature is 'missing' from MDA to AA to .... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www....

Open a Help File with a button
Hi All, I have created a help file (.hlp) and I want to have my users open it from a button the form. How would I perform this. I tried to run the automated button creation called RUN APP and it didn't seem to work. Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1 one approach: create a command button and look at the HyperlinkAddress property. Fill the property with the file path and, as long as the .hlp extension has a valid association in windows, a simple click should open i...

Nested If statement -- please help :)
Hi, I am trying to create a nested if .. I'm guessing this is what I need but it's not working I am trying to categorize total fees <$1,000,000 = A - <$1M Between $1,000,000 and $5,000,000 = B - $1M-$5M Between $5,000,000 and $10,000,000 = C - $5M-$10M Between $10,000,000 and $20,000,000 = C - $10M-$20M > $20,000,000 = E - $20M+ I started with this but it's not working? IF(I2<1000000,"A - <$1M",IF(I2>999999 and <4999999,"B - $1M-$5M",IF(I2>4999999 and <9999999,"$10M-$20M,0))) thank you!!! You can't use Letters in your ...

Help! All file menus and buttons have disappeard in Visio 2003
I''ve been using Visio 2003 for exactly one day. Somehow, all my File etc menus have disappeared along with every menu bar and button. And I see no way to get them back because there is no View menu for me to get to! I've tried all the F1-F12 keys - can someone help me bring my menus back??? Thanks Ben Hi Ben, Following knowledge base articles may help you to resolve the issue: http://support.microsoft.com/kb/305400 - Toolbar, toolbar button, or menu bar is missing in Visio http://support.microsoft.com/kb/291213 - Menus, toolbars, and stencils disappear in full-screen v...

Column/Row Formating
I am using Office XP which inclued Excel 2002; my problem is my row/column formating will occasionally change without any input from me. I have several cells merged and do not have width set to adjust for the amount of text,just text wrap. Any ideas, I have to keep correcting my formats. Thanks, Gary C. Gary Merged cells will allow "wrap text" from Format>Cells>Alignment but.......... Excel has a problem auto-fitting merged cells. You can resize manually or via Macro. Jim Rech has written code for this. http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkm...

HELP PLEASE #4
Hi, 1) How do I unblock attchments in outlook 2003? I think its aregistry setting somewhere. I would like to add a few file formats to the list of safe files. 2) I have a lot of folders and subfolders where I organise my email in outlook. I would like to stop outlook opening these folders every time I have a new message in them? Any ideas would help. Thanks Rad .. ...

Retrieve emails/addresses Help
I recently removed Windows XP and went back to Windows ME. Upon doing so I mistakingly lost all the emails and address updates made in Outlook Express made since I changed from Windows ME to XP (3 mths). Any ideas on how to retrieve those lost emails and addresses in Outlook Express? To go from XP to Me, you had to re-install the OS, which more than likely wiped out everything. Ray at work "phil horan" <philhoran@yahoo.com> wrote in message news:03ba01c3610e$904b3470$a401280a@phx.gbl... > I recently removed Windows XP and went back to Windows ME. > Upon doing so ...

Rookie Needs Help! #2
Never mind. Just figured it out. Sorry for taking up your time. Storema -- storema ----------------------------------------------------------------------- storeman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1536 View this thread: http://www.excelforum.com/showthread.php?threadid=31438 Isn't this list amazing. ESP -- Don Guillett SalesAid Software donaldb@281.com "storeman" <storeman.1fvg3z@excelforum-nospam.com> wrote in message news:storeman.1fvg3z@excelforum-nospam.com... > > Never mind. Just figured it out. Sorry for taking...

Help!! I have problem deleting 2500 rows of filtered rows!!!!
I have total of 156 columns and 3900 rows with 20 columns of formula for each row. My filtered result is 2500 rows. I tried to delete 2500 rows in my excel but it took me more than half an hour to delete it either by vba code or manually by excel. I tried using office clipboard manually, it paste the data as value and my formula isn't there anymore. Anybody has any idea how to write a vba program to solve this problem and how to write a office clipboard in vba. Please help... anybody... :( DESPERATE FOR THE ANSWER -- shirley_kee ---------------------------------------------------...

Microsoft Excel Filters (some rows don't filter)
Hi, I'm using tables in MS Excel 2007 (I also remember having similar issues with 2003). The table contains static data, text and formulas. I'm using the auto-filter function, but for some reason some of the rows aren't filtering properly. At the moment, the table has 70 rows in it, but when I filter, it doesn't filter the final 4 rows. (eg: I filter column D for the value 22, which works up to row 66, but the final 4 rows stay in the table regardless). Upon unfiltering the data, some of the rows remain hidden. I've no idea why this is happening. I've tried turn...

Hide or Protect Headers
How do I prevent others from changing the format of the header and footer. I tried protecting the sheet but people are still able to edit the header and footer. Did you lock the cells you want to be protected? Select all cells to protect, format cells, protection tab, check locked. Then protect worksheet & apply password then save. "RLD" wrote: > How do I prevent others from changing the format of the header and footer. I > tried protecting the sheet but people are still able to edit the header and > footer. I believe this can only be done through...

Excel help #2
Hi - I'm pretty familiar with most stuff in excel and I'd say I'm an intermediate user. I would like to learn how to do vba in excel. Does anyone know of any online help or tutorials that would teach me? If possible, I would like interactive suggestions. Thanks in advance. Newtechie David McRitchie's "Getting Started". Note links to other sites at the boittom of the page. http://www.mvps.org/dmcritchie/excel/getstarted.htm VBA Tutorial from Microsoft. http://support.microsoft.com/support/excel/content/vba101/default.asp There are many more, but these sho...

Duplicate Data -Adding information to another column in row & dele
I have a spreadsheet that I have sorted and contains duplicate cells of information within the same columns, but has needed information within the row. I need some help writing a formula that would allow me to do as discribed below down the entire worksheet: If Cell B2= B3, Then add value in cell E3 to cell E2, retaining the E2 cell vaue, using sometype of delimiter such as a comma or hard return to seperate the 2 values, then delete row 3 and move down through the entire worksheet. Any help or advice would be appreciated. Thanks, Jacky Del Hoyo Try this against a copy of your wor...

PLEASE HELP 05-17-10
Ok I am trying to make the document where if I enter "a" in one cell, the cell next to it will auto populate a given value that I define. Please let me know how I can do this!!! Thanks!!! If you have a table of values somewhere which determines what "a" is equivalent to, then you could use a VLOOKUP formula in the next cell. Hope this helps. Pete On May 17, 10:14=A0pm, sagenichole <sagenich...@discussions.microsoft.com> wrote: > Ok I am trying to make the document where if I enter "a" in one cell, the > cell next to it will auto p...

Help I can receive but not send
Hi, I've been trying to sort out my email for days and, having managed to send once, I just can't seem to make it happen anymore. I get the following message : Task 'mail.uniquecharm.co.uk - Sending and Receiving' reported error (0x800CCC78) : 'Unable to send the message. Please verify the e-mail address in your account properties. The server responded: ?30 authentication required - "Your email could not be sent. To fix this you must make a simple change to your email (known as SMTP authentication). For advice visit www.btyahoo.com/smtp"' ...

Hiding Cells
How do you hide cells without having to hide the entire column or rows? Hi McNiwram! You can hide the contents by formatting the font the same color as the background. You can hide formulas in the formula bar by using: Format > Cells > Protection Place check against "Hidden" OK Note that this doesn't come into effect until the worksheet is protected using the Tools menu. -- -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "McNiwram&...