Budget Planner - HELP!

[HardChargin]

I am creating a budget planner, Jan thru December are in columns G4 thru Q4 and the sheet is to wide to print. I want to do two things -

First - use a list box in the cell January is currently represented (cell: G4) to select any month and the following columns will now show the following months correctly. ie. G4 = Jan, H4 = Feb, etc. until I select G4 (list box) and select August now G4 = Aug, H4 = Sept etc.

Second - is use a command button or another object at the top of the sheet to allow me to hide and unhide a portion of the sheet to which will allow me to include possibly half the year and print on one sheet. I have seen this done with a requirement for the user to click on the button and then press ctrl+shft+1 to unhide and to hide click and press ctrl+0. I imagine there is quite a bit of code involved and I am not very experienced with Excel so I am sure this is one of those times I'm biting off more than I can chew. I am using Excel 2002 SP2.

Thanks in advance!

 

[just_jon]

Welcome to MrExcel - we'll need a bit more detail about the layout of your sheet.

See if you can download Colo's HTMLMaker at the bottom of the page and post a sample of your data.

 

[HardChargin]

First Timer

I have made some progress since I posted my first message. I just joined and didn't research to ensure my questions havn't been posted and answered...so I apologize. I have the code that accomplishes the hiding/unhiding of the columns and it works outside of one issue:

How do I assign the code to the command button? I havn't figured out how to establish the link for the button to the code?

I will attempt to dload the utility as you suggested....I am sure it will be useful in future projects.

Regards,

HC

 

[just_jon]

Do you have a button/control on your sheet now?

 

[HardChargin]

Yes, I do.

 

[just_jon]

What type control is it?

EDIT You should be able to

1] click View | Toolbars | Control toolbox
2] click Design mode in upper left of control screen
3] click on your button
4] click on view code on the controls screen

 

[HardChargin]

I created a command button off the control toolbar, right clicked it and inserted this code:

sub CommandButton_Click()
If Columns("J:Q").EntireColumn.Hidden=True Then
Columns("J:Q").EntireColumn.Hidden=False
ElseIf Columns("J:Q").EntireColumn.Hidden=False Then
Columns("J:Q").EntireColumn.Hidden=True
End If
End Sub

I would like to be able to click to hide and then click again to unhide but do not know what the code might be to accomplish the link.

 

[just_jon]

I created a command button off the control toolbar, right clicked it and inserted this code:

sub CommandButton_Click()
If Columns("J:Q").EntireColumn.Hidden=True Then
Columns("J:Q").EntireColumn.Hidden=False
ElseIf Columns("J:Q").EntireColumn.Hidden=False Then
Columns("J:Q").EntireColumn.Hidden=True
End If
End Sub

I would like to be able to click to hide and then click again to unhide but do not know what the code might be to accomplish the link.

What you have done above works for me; what results are you getting?

 

[batkosta]

This code should work with a form button:

Sub Hide_Unhide_Columns()
Columns("J:Q").EntireColumn.Hidden = _
Not Columns("J:Q").EntireColumn.Hidden
With ActiveSheet.Shapes(Application.Caller) _
.OLEFormat.Object.Characters
If Columns("J:Q").EntireColumn.Hidden = True _
Then .Text = "Show Columns" _
Else .Text = "Hide Columns"
End With
End Sub

The caption changes according to the status of the columns.
Haven't tried it with command button.

 

[HardChargin]

Thanks to all for the assistance on the hide/unhide problem.....I have the code correct and the button works!

My next problem is a little harder to explain......

I have the months of the year listed in columns (Jan to Dec in F:Q) and want to use a list box and macro in column F to allow for selection of any month; once a month is selected the list box reflects the selection (say June) and now the months are listed in F:Q as June to May. I want to make a chart that will show what the dollar amount is in row 4 (Income) and row 11 (Spending) for the month that is selected or clicked on.

Thanks for any assistance on this one!