Excel Dynamic Chart #1: Hide / Unhide Rows

I am making a bidding program and I have a VBA code written to hide/unhide cells based on a certain cells value. So if we have 10 things to bid we'd type "10" into A5 and it would unhide 10 rows. The problem is that when we do bids often times we do multiple bids, add/change orders, and rarely end up with the same products that we initially bid. I have it so that we can hide/unhide rows based on how many products we bid.

What I'm trying to accomplish is to hide any empty rows, which would be caused by us initially bidding something out and then taking it off the bid, as well as unhiding any rows that have a bid on it.

For example, lets say we bid 10 things, they end up not wanting 3 of them, which in the bid program is in rows 3, 5, and 7. We want to be able to automatically hide those rows but keep rows 1, 2, 4, 6, 8, 9, 10.

Here is a portion of the code I have so far, should be plenty to get the idea of where I'm at. The example above would be as if I initally put "10" into A5, deleted rows 3, 5 and 7 with data still in all the other rows (so even though A5 has value 10 in there there would only be 7 rows in that range showing, and if I put "5" into A5 there'd still be showing 7 rows). I'm pretty new to VBA, infact this is my first project, I only have about 5 hours into it and I'm basically learning as I go (this is like 1/10th of the whole code that I am posting). I am pretty sure I could make a button in the spreadsheet that would manually run a macro to hide/unhide the rows that I want, which is fine and I will do it if this isn't possible and/or I have to completely start the code over, but I am trying to make using this sheet as easy and "idiot proof" as possible as there will be multiple people using it.

Also, if im doing this completely assbackwards to begin with (like I said this is basically my first project in VBA), please let me know what would've been the "correct" way to do it.

Thanks in advance!



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
If Target.Value = NoValue Then
Range("A6:L26").EntireRow.Hidden = True
End If

If Target.Value = 1 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L7").EntireRow.Hidden = False
End If

If Target.Value = 2 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L8").EntireRow.Hidden = False
End If

If Target.Value = 3 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L9").EntireRow.Hidden = False
End If

If Target.Value = 4 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L10").EntireRow.Hidden = False
End If

If Target.Value = 5 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L11").EntireRow.Hidden = False
End If

If Target.Value = 6 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L12").EntireRow.Hidden = False
End If

If Target.Value = 7 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L13").EntireRow.Hidden = False
End If

If Target.Value = 8 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L14").EntireRow.Hidden = False
End If

If Target.Value = 9 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L15").EntireRow.Hidden = False
End If

If Target.Value = 10 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L16").EntireRow.Hidden = False
End If

End If

End Sub

Hi,

I have attached a file containing a feature I would like to use but do not know what it is called or where to find it. By clicking a + button users can unhide rows or columns. Click the - button and users can hide those same rows or columns?

Excel Feature.xls

Thanks in Advance,

Jordan

Hi,
I'm new to excel VBA.
Need to do the following in a worksheet "Inlet chevron"
If cell A3 = "Combination", then hide rows 7 to 64 and unhide rows 65 to 94
If cell A3 ="Single", then hide rows 65 to 94 and unhide rows 7 to 64
Appreciate if you could type it down so that I could copy it.
Thanks in Advance.

Hello!
Im new to the forum and new to VBA programming. Im creating a spreadheet which will allow a user to select several values from a drop down list, and based on their selection a corresponding row will either appear or be hidden. What makes this a tricky is that the user can enter a value in upto 4 cells.

The problem:
There is a 7 second pause when a user selects a value from one of the four target cells.

Appreciate any help you can offer!

The code:
(Im only showing 2 (ACH & GACH) of the 8 possible values from the drop-down)
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.Address = "$H$28" Or Target.Address = "$I$28" Or Target.Address = "$J$28" Or Target.Address = "$K$28" Then
    '   -------------   INSPECT FOR ACH   ------------------
       If Range("$H$28") = "ACH" Or Range("$I$28") = "ACH" Or Range("$J$28") = "ACH" Or Range("$K$28") = "ACH" Then
            Rows("64").Hidden = False
            Rows("78:79").Hidden = False
        Else
            Rows("64").Hidden = True
            Rows("78:79").Hidden = True
        End If
    '   -------------   INSPECT FOR GACH   ------------------
        If Range("$H$28") = "GACH" Or Range("$I$28") = "GACH" Or Range("$J$28") = "GACH" Or Range("$K$28") = "GACH" Then
            Rows("65").Hidden = False
            Rows("80:81").Hidden = False
        Else
            Rows("65").Hidden = True
            Rows("80:81").Hidden = True
        End If

I have a chart on the same page as the source data. The chart has five lines, and the user needs the option to select which lines to show. I would like to show check boxes beside each row of data. The user would be able to hide/unhide each line as needed. I have tried the following statements:

Sub Macro1()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Hidden = True
End Sub

There are no errors when I compile, but I get an error on the last statement at runtime. Any help would be appreciated. Is there a better way to accomplished this objective

Hi all,

second post in 2 days, I'm glad there are people out there who are so willing to help. Here's my conundrum, I have a fair few data sets for which each has a chart, I'm currently trying to create a macro for each data set that, when clicked once will show the chart and when clicked again it will hide the chart.

I do not want to hide/show all of charts in the sheet, just individual charts, each with a corresponding macro button doing what I said above.

Your help would be greatly appreciated! Thank you in advance!

Eddy

Hello,
I have Chart with a few series (1~6).
I would like to put Chechboxes under the shart with series names and checked series must be displayed on chart. when i uncheck some series it must disapear from chart.
Please, help me with this task.

Best regards

I am finalizing (or at least trying to) some code for basically one big data filter. Criteria is entered on the first sheet ('Production Well (Start Here)') and then a button is pressed. The code then runs through about six separate worksheets and applies some pivot filtering and some other minor things. Now, once the filters have been applied, I am attempting to have the code hide any charts in which the filters didn't produce the correct results. I placed a simple IF statement in a cell on each worksheet (worksheet titles "Injection 1" to "Injection 6" and the IF statement is in cell AB1). Next, I put code in to activate each worksheet and then test the cell AB1 for either 0 or 1 and then set to either visible or not visible based from this. Now, the problem is that upon pressing the button to activate all of the VBA code, everything runs fine and produces no errors, but every chart included in the code to make visible/make not visible is not visible. The kicker is, that if I simply just click the button to apply the same exact filter again, the chart visible code works. I am using Office 2010 on Windows 7. I'll attach the workbook to see if anyone can help out.
Thanks for any help!

Workbook:
http://dl.dropbox.com/u/10836787/Wol...20%281%29.xlsm

By the way, if anyone tests out the filter to see what is going on, any single digit number is to be preceded by a 0. Example: "01" (without the quotes of course.) Pattern "01" is one of the better filter criteria because it produces three worksheets in which the charts should be visible and three in which the charts should be hidden.

Hello there,

I'm quite new to all these macro functions, but I'm putting together a Q&A sheet for the sales team and it was getting quite messy so I'm building a new one which will hide information and when the user wants they can expand or unhide the rows with the data in it.

I did play with it a little and recorded a macro which can hide and unhide the selected section, but in the end if I insert rows above the section or inbetween topics everything slips.

How do I make a macro that will always stick with the certain section and not slip if i add rows above or within that section.

I have attached the file to show what I'm trying to do, but I'm a bit lost, hope someone can help me out. I'm a super new at macros so hope someone can help me.

Hello,

Attached is a picture of the right side of a worksheet. I am curious to find out how they are able to produce that convient linking of the rows so that they can be hidden and unhidden very quickly. This would be very helpful, if someone knew. I am not even sure what to call this so please help!

Thank you,

Devin Magnuson