Drop down bar to hide column according to the content of a cell

Hi all,

I need help. How can i write a macro such that
when the user select (e.g. 2 ) from the dropdown for, it hides column
G:Z
when the user select (e.g. 5) from the dropdown form, it hides Column
J:Z 


Thank you so much. 

Cheers,
Daphie

0
8/7/2006 3:45:09 AM
excel 39879 articles. 2 followers. Follow

5 Replies
317 Views

Similar Articles

[PageSpeed] 55

I dropped a combobox from the control toolbox toolbar onto a worksheet.

I assigned a range with all the values I wanted (0-10 for my test) to the
listfillrange.

Then I doubleclicked on that combobox (while in design mode) and used this code:

Option Explicit
Private Sub ComboBox1_Change()
    Dim HowManyCols As Long
    
    HowManyCols = Me.ComboBox1.Value
    
    Me.Range("f1:z1").EntireColumn.Hidden = False
    If HowManyCols > 0 Then
        Me.Range(Me.Range("e1").Offset(0, HowManyCols), "Z1") _
            .EntireColumn.Hidden = True
    End If
End Sub

Daphie wrote:
> 
> Hi all,
> 
> I need help. How can i write a macro such that
> when the user select (e.g. 2 ) from the dropdown for, it hides column
> G:Z
> when the user select (e.g. 5) from the dropdown form, it hides Column
> J:Z
> 
> Thank you so much.
> 
> Cheers,
> Daphie

-- 

Dave Peterson
0
petersod (12005)
8/7/2006 12:59:50 PM
Hi... I'm sorry to bother you again...

If the columns to be hidden is in another worksheet. How should I alter
the codes?
And I have a list of 20.
If select 1 will hide O:AZ
If select 2 will hide Q:AZ
If select 3 will hide S:AZ


Dave Peterson wrote:
> I dropped a combobox from the control toolbox toolbar onto a worksheet.
>
> I assigned a range with all the values I wanted (0-10 for my test) to the
> listfillrange.
>
> Then I doubleclicked on that combobox (while in design mode) and used this code:
>
> Option Explicit
> Private Sub ComboBox1_Change()
>     Dim HowManyCols As Long
>
>     HowManyCols = Me.ComboBox1.Value
>
>     Me.Range("f1:z1").EntireColumn.Hidden = False
>     If HowManyCols > 0 Then
>         Me.Range(Me.Range("e1").Offset(0, HowManyCols), "Z1") _
>             .EntireColumn.Hidden = True
>     End If
> End Sub
>
> Daphie wrote:
> >
> > Hi all,
> >
> > I need help. How can i write a macro such that
> > when the user select (e.g. 2 ) from the dropdown for, it hides column
> > G:Z
> > when the user select (e.g. 5) from the dropdown form, it hides Column
> > J:Z
> >
> > Thank you so much.
> > 
> > Cheers,
> > Daphie
> 
> -- 
> 
> Dave Peterson

0
8/10/2006 1:56:16 AM
O, Q, S, ...
skip a column in between?

Option Explicit
Private Sub ComboBox1_Change()

    'If select 1 will hide O:AZ
    'If select 2 will hide Q:AZ
    'If select 3 will hide S:AZ

    Dim HowManyCols As Long
    
    HowManyCols = Me.ComboBox1.Value
    
    With Worksheets("Sheet2")
        .Range("o1:az1").EntireColumn.Hidden = False
        If HowManyCols > 0 Then
            .Range(.Range("m1").Offset(0, (2 * HowManyCols)), "aZ1") _
                .EntireColumn.Hidden = True
        End If
    End With
End Sub




Daphie wrote:
> 
> Hi... I'm sorry to bother you again...
> 
> If the columns to be hidden is in another worksheet. How should I alter
> the codes?
> And I have a list of 20.
> If select 1 will hide O:AZ
> If select 2 will hide Q:AZ
> If select 3 will hide S:AZ
> 
> Dave Peterson wrote:
> > I dropped a combobox from the control toolbox toolbar onto a worksheet.
> >
> > I assigned a range with all the values I wanted (0-10 for my test) to the
> > listfillrange.
> >
> > Then I doubleclicked on that combobox (while in design mode) and used this code:
> >
> > Option Explicit
> > Private Sub ComboBox1_Change()
> >     Dim HowManyCols As Long
> >
> >     HowManyCols = Me.ComboBox1.Value
> >
> >     Me.Range("f1:z1").EntireColumn.Hidden = False
> >     If HowManyCols > 0 Then
> >         Me.Range(Me.Range("e1").Offset(0, HowManyCols), "Z1") _
> >             .EntireColumn.Hidden = True
> >     End If
> > End Sub
> >
> > Daphie wrote:
> > >
> > > Hi all,
> > >
> > > I need help. How can i write a macro such that
> > > when the user select (e.g. 2 ) from the dropdown for, it hides column
> > > G:Z
> > > when the user select (e.g. 5) from the dropdown form, it hides Column
> > > J:Z
> > >
> > > Thank you so much.
> > >
> > > Cheers,
> > > Daphie
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
8/10/2006 2:12:58 AM
Btw, what is "m1" meant for??

And...

My combo box is inserted in excel worksheet, not in the design mode.
If i were to create a userform in the design mode, how do i insert and
place  in onto the worksheet??

Thanks a lot

Dave Peterson wrote:
> O, Q, S, ...
> skip a column in between?
>
> Option Explicit
> Private Sub ComboBox1_Change()
>
>     'If select 1 will hide O:AZ
>     'If select 2 will hide Q:AZ
>     'If select 3 will hide S:AZ
>
>     Dim HowManyCols As Long
>
>     HowManyCols = Me.ComboBox1.Value
>
>     With Worksheets("Sheet2")
>         .Range("o1:az1").EntireColumn.Hidden = False
>         If HowManyCols > 0 Then
>             .Range(.Range("m1").Offset(0, (2 * HowManyCols)), "aZ1") _
>                 .EntireColumn.Hidden = True
>         End If
>     End With
> End Sub
>
>
>
>
> Daphie wrote:
> >
> > Hi... I'm sorry to bother you again...
> >
> > If the columns to be hidden is in another worksheet. How should I alter
> > the codes?
> > And I have a list of 20.
> > If select 1 will hide O:AZ
> > If select 2 will hide Q:AZ
> > If select 3 will hide S:AZ
> >
> > Dave Peterson wrote:
> > > I dropped a combobox from the control toolbox toolbar onto a worksheet.
> > >
> > > I assigned a range with all the values I wanted (0-10 for my test) to the
> > > listfillrange.
> > >
> > > Then I doubleclicked on that combobox (while in design mode) and used this code:
> > >
> > > Option Explicit
> > > Private Sub ComboBox1_Change()
> > >     Dim HowManyCols As Long
> > >
> > >     HowManyCols = Me.ComboBox1.Value
> > >
> > >     Me.Range("f1:z1").EntireColumn.Hidden = False
> > >     If HowManyCols > 0 Then
> > >         Me.Range(Me.Range("e1").Offset(0, HowManyCols), "Z1") _
> > >             .EntireColumn.Hidden = True
> > >     End If
> > > End Sub
> > >
> > > Daphie wrote:
> > > >
> > > > Hi all,
> > > >
> > > > I need help. How can i write a macro such that
> > > > when the user select (e.g. 2 ) from the dropdown for, it hides column
> > > > G:Z
> > > > when the user select (e.g. 5) from the dropdown form, it hides Column
> > > > J:Z
> > > >
> > > > Thank you so much.
> > > >
> > > > Cheers,
> > > > Daphie
> > >
> > > --
> > >
> > > Dave Peterson
> 
> -- 
> 
> Dave Peterson

0
8/10/2006 4:36:52 AM
M1 is the cell in column M, row 1--it's just an address.

If you do a little math, then you'll see what this does:

..Range("m1").Offset(0, (2 * HowManyCols))

If you put 1 in the combobox, then Howmany = 1
so this becomes

..Range("m1").Offset(0, (2 * 1))
or
..Range("m1").Offset(0, 2)

..offset(0,2) means stay on the same row, but go two columns to the right.

Two columns to the right of M1 is O1.

And if you used a combobox from the control toolbox toolbar, you'll see that
there's an icon on that control toolbox toolbar that allows you to go into
design mode--to move the control, to double click on it and get to the VBE (to
change the code).


Daphie wrote:
> 
> Btw, what is "m1" meant for??
> 
> And...
> 
> My combo box is inserted in excel worksheet, not in the design mode.
> If i were to create a userform in the design mode, how do i insert and
> place  in onto the worksheet??
> 
> Thanks a lot
> 
> Dave Peterson wrote:
> > O, Q, S, ...
> > skip a column in between?
> >
> > Option Explicit
> > Private Sub ComboBox1_Change()
> >
> >     'If select 1 will hide O:AZ
> >     'If select 2 will hide Q:AZ
> >     'If select 3 will hide S:AZ
> >
> >     Dim HowManyCols As Long
> >
> >     HowManyCols = Me.ComboBox1.Value
> >
> >     With Worksheets("Sheet2")
> >         .Range("o1:az1").EntireColumn.Hidden = False
> >         If HowManyCols > 0 Then
> >             .Range(.Range("m1").Offset(0, (2 * HowManyCols)), "aZ1") _
> >                 .EntireColumn.Hidden = True
> >         End If
> >     End With
> > End Sub
> >
> >
> >
> >
> > Daphie wrote:
> > >
> > > Hi... I'm sorry to bother you again...
> > >
> > > If the columns to be hidden is in another worksheet. How should I alter
> > > the codes?
> > > And I have a list of 20.
> > > If select 1 will hide O:AZ
> > > If select 2 will hide Q:AZ
> > > If select 3 will hide S:AZ
> > >
> > > Dave Peterson wrote:
> > > > I dropped a combobox from the control toolbox toolbar onto a worksheet.
> > > >
> > > > I assigned a range with all the values I wanted (0-10 for my test) to the
> > > > listfillrange.
> > > >
> > > > Then I doubleclicked on that combobox (while in design mode) and used this code:
> > > >
> > > > Option Explicit
> > > > Private Sub ComboBox1_Change()
> > > >     Dim HowManyCols As Long
> > > >
> > > >     HowManyCols = Me.ComboBox1.Value
> > > >
> > > >     Me.Range("f1:z1").EntireColumn.Hidden = False
> > > >     If HowManyCols > 0 Then
> > > >         Me.Range(Me.Range("e1").Offset(0, HowManyCols), "Z1") _
> > > >             .EntireColumn.Hidden = True
> > > >     End If
> > > > End Sub
> > > >
> > > > Daphie wrote:
> > > > >
> > > > > Hi all,
> > > > >
> > > > > I need help. How can i write a macro such that
> > > > > when the user select (e.g. 2 ) from the dropdown for, it hides column
> > > > > G:Z
> > > > > when the user select (e.g. 5) from the dropdown form, it hides Column
> > > > > J:Z
> > > > >
> > > > > Thank you so much.
> > > > >
> > > > > Cheers,
> > > > > Daphie
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
8/10/2006 12:36:10 PM
Reply:

Similar Artilces:

Right Click to Autofill cells in 2003
Hello All, In 97 to 2002 it was possible to right click when using Autofill to copy the cells down rather than continuing the sequence. in 2003 i just get a pretty red line (what purpose does this serve?). Does anyone know if I can get back the old functionality? Many thanks, Danny I have that functionality in Excel 2003 as well, maybe you have installed an add-in? In any case if you hold down ctrl while using the left click copy down it will also copy as opposed to fill a series Regards, peo sjoblom "DannyJ" wrote: > Hello All, > > In 97 to 2002 it was possi...

How do I randomly shuffle the cells in a column?
In Excel 2003, I have a column, say a1-a10, with certain entries. I need to create another column, b1-b10, with the same entries assigned randomly (shuffled) to the cells. Just one way .. Put in B1: =INDEX(A:A,RANK(C1,$C$1:$C$10)) Put in C1: =RAND() Select B1:C1, copy down to C10 B1:B10 returns a random shuffle of what's in A1:A10 Press F9 to re-shuffle -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jason D" <Jason D@discussions.microsoft.com> wrote in message news:36244977-7EE5-436B-8617-59C15310B080@microsoft.com... >...

Delete Columns
In Excel, I need a macro that will delete a column based on a cell that will contain a name based on an input box. Thanks, Bernie See you OTHER post. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Bernie" <bml66@aol.com> wrote in message news:1172699736.687460.300590@h3g2000cwc.googlegroups.com... > In Excel, I need a macro that will delete a column based on a cell > that will contain a name based on an input box. > > Thanks, > Bernie > ...

How to change cell shading/fill based on value in another cell?
I have a sheet with two columns of experimental data. Let's say the data is in F10:F25 (column 1) & G10:G25 (column 2). There is a value in another cell (A1) that contains a 1 or a 2. If A1=1, I want to turn shading in column 1 agree and in column 2 off. If A1=2, I want to reverse that. If there a worksheet function that will do that? I was hoping for something like: =cellshading(range,color) I could then execute one of these two statements: If A1=1 then cellshading(F10:F25,"Green") cellshading(G10:G25,"None") Else cellshading(G10:G25,"Green&...

Hide Classifications in Money 2007?
Hi, I was wondering if it is possible to hide the classification line in the advanced register of Money 2007. In earlier versions, if no classes were defined, the line would not show. Thanks, --Mark In all versions I've used (that's every one since Mv2) classification shows if classification1 is defined. Somehow I'm suspecting your classification1 got defined. Account List | Categories & Payees . Click on the first classification. If it doesn't ask you to define it, there will be a choice for Delete classification. That's where you'd want to go. "...

add result column to pivot table
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. Add another copy of the Amount field to the pivot table's data area. To display, the fields horizontally, drag the Data button, onto the cell that contains the word 'Total' -- there's a picture here: http://www.contextures.com/xlPivot02.html Right-click on the heading for the second Amount column Choose Field Settings Click the Options button Type a Name for the field, e.g. Percent Choose to 'Show Data as' % of column Cli...

How keep chart-datapoints displayed when column-width is set to zero
Hello there, my data basis for a chart has one column, that I don't want to be shown in the spreadsheet. However, when I set this column's width to zero, the correspondig data dissapear from the chart. How can I have both, a hidden column and its values shown in the chart at the same time ? Kind regards, H.G. Lamy H.G. - Select the chart, then select Options from the Tools menu, click on the Chart tab, and UNcheck the Plot Visible Cells Only checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ hglamy wrote: &...

increase column number limit?
Is there a way to increase the number of columns beyond ~260? Thanks /b No, sorry. Dan E "bill northlich" <billn@wildpackets.com> wrote in message news:eE3bpxpYDHA.212@TK2MSFTNGP12.phx.gbl... > Is there a way to increase the number of columns beyond ~260? Thanks > /b > 256 is the max. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference -----------...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

Sum Multiple Worksheets, same named cell
I have multiple worksheets containing the same named cell (e.g. Month_Total). On a totals worksheet, I am not able to use =SUM('START,END'!Month_Total) because of the local/global naming. Is there a way around this? (Month_Total will not always be in the same cell on each worksheet). Thanks! It seems to me that you have two options. 1. Name each of your "Monthly Total"s slightly different. OR 2. Do not use the cell range naming. "uncreative" <uncreative@discussions.microsoft.com> wrote in message news:60A150C6-50BC-4876-A31F-1C...

Each new window showing up in start bar--want to turn this off
Hi, I'm a new user to Outlook. Everytime I open a new email it puts a new item on my Start bar. Now my Start bar is cluttered because I have more than one message open at once. Where is the option to change this? -- Thanks, PTweety "pickytweety" <pickytweety@discussions.microsoft.com> wrote in message news:42E83CF7-E369-4CB5-935C-2058FF39D4E7@microsoft.com... > I'm a new user to Outlook. Everytime I open a new email it puts a new item > on my Start bar. Now my Start bar is cluttered because I have more than one > message open at once. Where is the...

Caption bar disappears suddenly
Hi, While working with windows application(Win XP, SP2) suddenly caption bar disappears for all the windows. This occurs only when the theme is Windows XP(modified). When we switch to "Windows classic" theme this problem does not occur. Have any one come across this problem? Is there any patch from Windows? Thanks & Regards, VSP Dont know about the problem but MFC has nothing to do with this. --- Ajay "VSP" <VSP@VSP.com> wrote in message news:fi5pmh$m2e$1@daniel-new.mch.sbs.de... > Hi, > > While working with windows application(Win XP, SP2) su...

How to group all data from 1 column into several columns (Redo)
I have 2 columns in my spreadsheet, Company Names and Event. The Event column values are Event 1, Event 2, Event 3, Event 4 and the company names repeat for each of the events that they attended. So sometimes I might have a company listed 4 times with each Event corresponding to it in the next column. How can I consolidate all of the company names so that there is 1 row for each company and all events are on the same row but in different columns marked with an x (the column labels will be Company name, Event 1, Event 2, Event 3, Event 4). This is what I have... Company Name...

Columns to Rows
I'm sure this is a simple problem but I'm trying to convert columns o data into rows of data. What's the easiest way -- Message posted from http://www.ExcelForum.com Hi try the following: - copy your column (CTRL+C) - goto 'Edit - Paste Special) and choose 'Transpose' as action Frank > I'm sure this is a simple problem but I'm trying to convert columns of > data into rows of data. What's the easiest way? > > > --- > Message posted from http://www.ExcelForum.com/ Just read Tom's post, paste special and then transpose -- Messag...

charting a single bar graph on one line showing continuous info
I must do a time graph showing total workhours with tick marks showing begin and end of tours on a dialy basis. I cannot get the custom or standard chart graph functions to do this without putting in all of the data. I just want the begin tour and end tour of each person. can you help. thx debi ...

Overlay 4 years of data as a line on 4 years of columns for several x category labels
Dear Charting aficianados, The tutorial at http://peltiertech.com/Excel/Charts/ClusterColAndLine.html is the closest example I can find to a charting conundrum I am facing, though what I am hoping to achieve is still a little different. I've made a mock up of what I'm hoping to achieve - though I have had to use drawing objects for the line series, to overlay on the columns - I'm sure there is a way to get excel to do this - do you think there is? (sheet named mock-up) A workbook with mocuk-up and my other charting attmempts can be found: http://www.savefile.com/files/2690840 T...

hide activities
Hi, I'd like to hide the service appointment and campaign response activities options when I want to create a new activity. I was change the dlg_create.aspx file and comment the render list item. I don't like the idea to manipulate this file, and I want to know if is possible to hide this options in other way. thanks -- Magy yes you can hid see your id's document.all.navContacts.style.display = "none"; -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "...

Algebra within a cell
How do you set-up a formula in a cell that multiplies a constant times the number you insert? Ex. the constant is .315 remains present at all times only the number you insert changes - =.315*(x) You can't unless you use an event macro, if you need a formula you have to use another cell as help =0.315*A2 where A2 holds x you can also put 0.315 in a cell, copy it, select the cel with x and then paste special and select multiply. But to get this instantly you have to use something like Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2"), Target)...

Error bars not displaying properly
Hi, I have plotted my data in a column graph, and added error bars based on custom values. I know how to edit and format the error bars, but nothing I have changed fixes my problem: The error bars "join up" to give diagonal lines between between my columns. I have NEVER had this issue in previous versions of Excel. If I plot the error bars in pre-2007 excel, they're fine, until I open them in excel-2007. If I remove the error bars, and plot them afresh in excel-2007, I get the same problem. It doesn't happen to every error bar, just some of them, for no apparent ...

is there a way to make the bars on a bar chart narrower?
help anyone? -- mcarrington ------------------------------------------------------------------------ mcarrington's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21641 View this thread: http://www.excelforum.com/showthread.php?threadid=498513 Hi mcarrington, Have you tried right clicking a bar then Format Data Series then change the Gap width? Ken Johnson Hi mcarrington, sorry, I left out one step... Right click a bar>Format Data Series>Options>change Gap Width. Ken Johnson ...

Drop down box with country list
I have an excel application where I need a drop down box with all the country' names listed, ready to use (like the Microsoft Date and Time Picker Control). Is there a way to get it and how? John -- john liem Enter the countries in a range (say column M) and then add a Data Validation (Data>Validation) and select the List option with a formula of say =M1:M100 or however many countries -- HTH Bob Phillips "john liem" <john.liem.1p8hhm@news.officefrustration.com> wrote in message news:john.liem.1p8hhm@news.officefrustration.com... > > I have an excel applic...

Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec
I need a way to fix a print problem. When I go into File - Page Setup, and I choose which rows to repeat at the top ($1:$5) and select which columns to repeat at left ($A:$A), the rows at the top never print completely on pages 2 through the end. Only on the first page can I see the entire title and subtitle. On the remaining pages they get cut off where Column B would begin. Can I fix this? Hi Select only the rows to repeat at the top, and see if that solves your problem -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "unibaby" wrote: > ...

Displaying Multiple Cell Information in Single Cell
Hi all, I'm trying to present (text) data from multiple cells in another workbook in a single cell. I've tried the following formula but it returns a #VALUE! in my destination cell: ='[Project1.xls]Dependencies'!$A$6, '[Project1.xls]Dependencies'!$A$7 Any ideas on if this is possible? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=503954 Use the "&...

sorting text in 2 columns by likeness
I have 2 columns of text with number values associated with them. Amy 21 Amanda 12 Bill 12 Amy 18 Chris 32 Bill 23 Kathy 8 Chris 12 Kim 15 Curtis 32 Todd 23 Katrhy 21 Todd 5 I need to be able to sort these like this Amanda 12 Amy 21 Amy 18 Bill 12 Bill 23 Chris 32 Chris ...

Formatting cells
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel All of a sudden when I try to format a column of or an individual cell (format as a number, no decimal places, comma for thousands) only 2 of the four numbers appear in the cell. When I double click to view what is actually in the cell I see the four numbers I entered, the first two separated by the last two with a decimal. <br> I have tried this on new sheets and workbooks and the same thing happens. I can't tell for sure, but try going to Excel>Preferences> Edit. If there's a check on '...