Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Assigning macro to button.
I have 4 colums named Yr1 Yr2 Yr3 and 3yr Summary all from A to D. Nex
to those colums starting from E are hidden colums all having mor datailed info from each of the 12 months of that certain year. What don't know how to do is to assign the correct macro that when I clic on one of those buttons (for example Yr1) I want it to go straight t those 12 colums with each of the months but I want it to show th "view" of all 12 months on 1 page so that when someone prints it ou just those 12 months will be printed out. I also want all those buttons to stay in place and not go away once I click on them. So i someone can help me with the code for the macro that can send m straight to the detailed 12 months "view" once I click on the button appreciate it -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Assigning macro to button.
It's not very likely I've fully understood your request. I
hope the following attempt will at least get the ball rolling. Quote 1: "I have 4 colums named Yr1 Yr2 Yr3 and 3yr Summary all from A to D." Quote 2: "What I don't know how to do is to assign the correct macro that when I click on one of those buttons (for example Yr1) I want it to go straight to those 12 colums with each of the months ..." My take on the above Quotes 1 and 2 is that Columns A through D are not named ranges but instead each have a button at the top with their captions being "Yr1", "Yr2", "Yr3" and "3Yr Summary" respectively. Quote 3: "Next to those colums starting from E are hidden colums all having more datailed info from each of the 12 months of that certain year." My take on Quote 3 is that: i) Columns E through P contain the detailed info for each of the months for Yr1. ii) Columns Q through AB contain the detailed info for each of the months for Yr2. iii) Columns AC through AN contain detailed info for each of the months for Yr3. My code makes the following assumptions: i) The sheet you refer to is named "Year Info". ii) The sheet you want the data transferred to is named "Report". iii) Cells A1, B1 and C1 each have a command button (or some other shape object) contained within the boundaries of the cell and that the below macro is assigned to these buttons. To demo the code, ensure that the top-left corner of each button is contained within the cell. iv) You want the data to be transferred to Columns A through L of the "Report" sheet. v) You want to set the print area to this range and to activate the sheet. Sub TransferYearData() Dim C As Range, Rng1 As Range, Rng2 As Range Dim Rw As Long, Col As Integer With Sheets("Year Info") Set C = .Shapes(Application.Caller).TopLeftCell Col = C.Column Rw = .UsedRange.Rows.Count Set Rng1 = .Cells(1, Col * 12 - 7).Resize(Rw, 12) End With With Sheets("Report") .Columns("A:L").ClearContents Set Rng2 = .Cells(1, 1).Resize(Rw, 12) Rng2.Value = Rng1.Value .PageSetup.PrintArea = Rng2.Address .Activate End With End Sub Regards, Greg -----Original Message----- I have 4 colums named Yr1 Yr2 Yr3 and 3yr Summary all from A to D. Next to those colums starting from E are hidden colums all having more datailed info from each of the 12 months of that certain year. What I don't know how to do is to assign the correct macro that when I click on one of those buttons (for example Yr1) I want it to go straight to those 12 colums with each of the months but I want it to show the "view" of all 12 months on 1 page so that when someone prints it out just those 12 months will be printed out. I also want all those 4 buttons to stay in place and not go away once I click on them. So if someone can help me with the code for the macro that can send me straight to the detailed 12 months "view" once I click on the button I appreciate it. --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Assigning macro to button.
Thanks Greg. Your subroutine worked just perfectly. Thanks agai
-- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a macro to a button | Excel Worksheet Functions | |||
Assigning A Macro to A Button | Excel Worksheet Functions | |||
assigning a macro to a button | Excel Discussion (Misc queries) | |||
Assigning macro to button | Excel Discussion (Misc queries) | |||
Assigning a macro to a "button" | Excel Discussion (Misc queries) |