|
Macro runs fine from VBEditor, but not button
Hi, this macro was running fine, until the conversion to Office 2007. In
the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety |
Macro runs fine from VBEditor, but not button
If your file extension in xl2007 is .xlsx then it has no macro in it. It
would have to have a .xlsm file extension fo contain a macro. "pickytweety" wrote in message ... Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety |
Macro runs fine from VBEditor, but not button
The ".xlsx" file is created when I copy some "post-macro" sheets (that the
macro creates) into a new book. In other words, I click the macro, new sheets are created. The macro stops. I copy the newly created sheets into a new book (this is where the .xlsx comes from). I go back to the original file and try to run the macro to create more sheets (for the next zone) but this time it gives me an error saying it can't find the macro...but that's because it's looking for it in the new book (even if it's closed), not the original file. Why it tries to find the macro in a new book is what has got me baffled. I've tried closing the new book and it still won't work. I tried saving the original .xls file as an .xlsm to see if it made a difference and it doesn't. If I try to run the macro twice using the button, the second time it can't find the macro. It's so weird. Keep in mind that this has been running for a year with no trouble, then we switch to 2007 and it won't work with the button after the first zone. Thanks, PTweety "JLGWhiz" wrote: If your file extension in xl2007 is .xlsx then it has no macro in it. It would have to have a .xlsm file extension fo contain a macro. "pickytweety" wrote in message ... Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety . |
Macro runs fine from VBEditor, but not button
I'll try to be clearer. You cannot call a macro from a .xlsx file. It has
to have been saved as a .xlsm file to retain the macro. "pickytweety" wrote in message ... The ".xlsx" file is created when I copy some "post-macro" sheets (that the macro creates) into a new book. In other words, I click the macro, new sheets are created. The macro stops. I copy the newly created sheets into a new book (this is where the .xlsx comes from). I go back to the original file and try to run the macro to create more sheets (for the next zone) but this time it gives me an error saying it can't find the macro...but that's because it's looking for it in the new book (even if it's closed), not the original file. Why it tries to find the macro in a new book is what has got me baffled. I've tried closing the new book and it still won't work. I tried saving the original .xls file as an .xlsm to see if it made a difference and it doesn't. If I try to run the macro twice using the button, the second time it can't find the macro. It's so weird. Keep in mind that this has been running for a year with no trouble, then we switch to 2007 and it won't work with the button after the first zone. Thanks, PTweety "JLGWhiz" wrote: If your file extension in xl2007 is .xlsx then it has no macro in it. It would have to have a .xlsm file extension fo contain a macro. "pickytweety" wrote in message ... Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety . |
Macro runs fine from VBEditor, but not button
I'm not quite sure what you're doing, but it sounds like the macro is assigned
to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson |
Macro runs fine from VBEditor, but not button
Follow up: When your file were transferred from the older version into
xl2007, if they contained macros and were not saved as .xlsm file types, then you could very well have lost your macros. You need to check with your IT folks for back-up if the macros were lost. "pickytweety" wrote in message ... The ".xlsx" file is created when I copy some "post-macro" sheets (that the macro creates) into a new book. In other words, I click the macro, new sheets are created. The macro stops. I copy the newly created sheets into a new book (this is where the .xlsx comes from). I go back to the original file and try to run the macro to create more sheets (for the next zone) but this time it gives me an error saying it can't find the macro...but that's because it's looking for it in the new book (even if it's closed), not the original file. Why it tries to find the macro in a new book is what has got me baffled. I've tried closing the new book and it still won't work. I tried saving the original .xls file as an .xlsm to see if it made a difference and it doesn't. If I try to run the macro twice using the button, the second time it can't find the macro. It's so weird. Keep in mind that this has been running for a year with no trouble, then we switch to 2007 and it won't work with the button after the first zone. Thanks, PTweety "JLGWhiz" wrote: If your file extension in xl2007 is .xlsx then it has no macro in it. It would have to have a .xlsm file extension fo contain a macro. "pickytweety" wrote in message ... Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety . |
Macro runs fine from VBEditor, but not button
It was the form control button I was using, I think. So let me see if I
understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . |
Macro runs fine from VBEditor, but not button
With the button from the Control Toolbox, when you right click the button in
design mode, repeat: in design mode, you will be able to select view code from the pop up menu. Click on View Code and it will open the proper code window where you should see a two line of code similar to this: Private Sub CommandButton1_ Click() End Sub Put your old macro between these two lines and remove the title line of the old macro as well as the duplicate End Sub. Now when you click the button the code will run from the object that you have the button on (i.e. Sheet or UserForm) As an alternative, If you want to leave your macro in the public module, then just use the click event to call that macro as below. Private Sub CommandButton1_Click() 'your macro name here End Sub "pickytweety" wrote in message ... It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . |
Macro runs fine from VBEditor, but not button
I'm confuse about where this button is--and what type it is.
If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . -- Dave Peterson |
Changing text on button from "CommandButton1" to "Prepare Report"
Ok in Project Explorer, my macro is located in the Sheet2 rather than the
Module section. How will I know for the future if I should be putting code in the sheet or in a module? Also, I want my button to say "Prepare Report" rather than "CommandButton1", but I can't figure out how to change the text on the button. -- Thanks, PTweety "JLGWhiz" wrote: With the button from the Control Toolbox, when you right click the button in design mode, repeat: in design mode, you will be able to select view code from the pop up menu. Click on View Code and it will open the proper code window where you should see a two line of code similar to this: Private Sub CommandButton1_ Click() End Sub Put your old macro between these two lines and remove the title line of the old macro as well as the duplicate End Sub. Now when you click the button the code will run from the object that you have the button on (i.e. Sheet or UserForm) As an alternative, If you want to leave your macro in the public module, then just use the click event to call that macro as below. Private Sub CommandButton1_Click() 'your macro name here End Sub "pickytweety" wrote in message ... It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . . |
Macro runs fine from VBEditor, but not button
Hi Dave,
The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . -- Dave Peterson . |
Macro runs fine from VBEditor, but not button
Show that control toolbox toolbar again.
Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Macro runs fine from VBEditor, but not button
I'm in design mode (the icon with the ruler, triangle and pencil is orange
instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . -- Dave Peterson |
Macro runs fine from VBEditor, but not button
I don't know why you're not seeing the options...
But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select End Sub -- Thanks, PTweety -- Dave Peterson . -- Dave Peterson -- Dave Peterson |
Macro runs fine from VBEditor, but not button
I tried clicking the properties box already to look for the "caption" you
mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" |
Macro runs fine from VBEditor, but not button
I use a newsreader to access the newsgroups. So I don't see that "notifiy me of
replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to move the Dist Total ' and the Total Company sheet in. Sheets("Dist Ttl").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Sheets("Ttl Co").Activate ActiveSheet.Calculate ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ActiveSheet.Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" -- Dave Peterson |
Macro runs fine from VBEditor, but not button
So I tried the immediate window code and this message box popped up:
Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) |
Macro runs fine from VBEditor, but not button
Either the wrong worksheet was active--or the name of the commandbutton is not
"commandbutton1". What do you see in the namebox with the commandbutton selected? pickytweety wrote: So I tried the immediate window code and this message box popped up: Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation store = .Range("a5").Value End With 'Create new sheet for location (store) -- Dave Peterson |
Macro runs fine from VBEditor, but not button
Even though the Design mode icon is highlighted and the CommandButton1 has
the eight sizing bubbles, when I click on properties, the name is Sheet2 Worksheet. I click on the button again and nothing happens --it's like a can't get to the properties of the button, no matter what I try. I have the screen split and I can see the button and Visual Basic at the same time. Sheet2 (I call it Locations) is the selected sheet and the CommandButton1 appears to be selected because it has those sizing bubbles. -- Thanks, PTweety "Dave Peterson" wrote: Either the wrong worksheet was active--or the name of the commandbutton is not "commandbutton1". What do you see in the namebox with the commandbutton selected? pickytweety wrote: So I tried the immediate window code and this message box popped up: Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) |
Macro runs fine from VBEditor, but not button
Try creating a new workbook and see if that problem persists.
I really don't have a guess why you're having trouble. pickytweety wrote: Even though the Design mode icon is highlighted and the CommandButton1 has the eight sizing bubbles, when I click on properties, the name is Sheet2 Worksheet. I click on the button again and nothing happens --it's like a can't get to the properties of the button, no matter what I try. I have the screen split and I can see the button and Visual Basic at the same time. Sheet2 (I call it Locations) is the selected sheet and the CommandButton1 appears to be selected because it has those sizing bubbles. -- Thanks, PTweety "Dave Peterson" wrote: Either the wrong worksheet was active--or the name of the commandbutton is not "commandbutton1". What do you see in the namebox with the commandbutton selected? pickytweety wrote: So I tried the immediate window code and this message box popped up: Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' ' PrepareReport Macro ' ' Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) -- Dave Peterson |
Macro runs fine from VBEditor, but not button
I was able to change the caption in a new workbook.
-- Thanks, PTweety "Dave Peterson" wrote: Try creating a new workbook and see if that problem persists. I really don't have a guess why you're having trouble. pickytweety wrote: Even though the Design mode icon is highlighted and the CommandButton1 has the eight sizing bubbles, when I click on properties, the name is Sheet2 Worksheet. I click on the button again and nothing happens --it's like a can't get to the properties of the button, no matter what I try. I have the screen split and I can see the button and Visual Basic at the same time. Sheet2 (I call it Locations) is the selected sheet and the CommandButton1 appears to be selected because it has those sizing bubbles. -- Thanks, PTweety "Dave Peterson" wrote: Either the wrong worksheet was active--or the name of the commandbutton is not "commandbutton1". What do you see in the namebox with the commandbutton selected? pickytweety wrote: So I tried the immediate window code and this message box popped up: Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' |
Macro runs fine from VBEditor, but not button
Oh, guess what. When it worked in the new sheet it made me wonder.....maybe
the other forms control button is interfering. So I deleted the original button (I hadn't previously deleted the forms control button...I just created a new CommandButton) and drew the new one and it let me change the caption. Thanks for your help. -- Thanks, PTweety "Dave Peterson" wrote: Try creating a new workbook and see if that problem persists. I really don't have a guess why you're having trouble. pickytweety wrote: Even though the Design mode icon is highlighted and the CommandButton1 has the eight sizing bubbles, when I click on properties, the name is Sheet2 Worksheet. I click on the button again and nothing happens --it's like a can't get to the properties of the button, no matter what I try. I have the screen split and I can see the button and Visual Basic at the same time. Sheet2 (I call it Locations) is the selected sheet and the CommandButton1 appears to be selected because it has those sizing bubbles. -- Thanks, PTweety "Dave Peterson" wrote: Either the wrong worksheet was active--or the name of the commandbutton is not "commandbutton1". What do you see in the namebox with the commandbutton selected? pickytweety wrote: So I tried the immediate window code and this message box popped up: Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' |
Macro runs fine from VBEditor, but not button
I'm betting that the name of that original button wasn't Commandbutton1. But
it's too late to know for sure <vbg. Glad you got it working. pickytweety wrote: Oh, guess what. When it worked in the new sheet it made me wonder.....maybe the other forms control button is interfering. So I deleted the original button (I hadn't previously deleted the forms control button...I just created a new CommandButton) and drew the new one and it let me change the caption. Thanks for your help. -- Thanks, PTweety "Dave Peterson" wrote: Try creating a new workbook and see if that problem persists. I really don't have a guess why you're having trouble. pickytweety wrote: Even though the Design mode icon is highlighted and the CommandButton1 has the eight sizing bubbles, when I click on properties, the name is Sheet2 Worksheet. I click on the button again and nothing happens --it's like a can't get to the properties of the button, no matter what I try. I have the screen split and I can see the button and Visual Basic at the same time. Sheet2 (I call it Locations) is the selected sheet and the CommandButton1 appears to be selected because it has those sizing bubbles. -- Thanks, PTweety "Dave Peterson" wrote: Either the wrong worksheet was active--or the name of the commandbutton is not "commandbutton1". What do you see in the namebox with the commandbutton selected? pickytweety wrote: So I tried the immediate window code and this message box popped up: Run-time error '438': Object doesn't support this property or method. -- Thanks, PTweety "Dave Peterson" wrote: I use a newsreader to access the newsgroups. So I don't see that "notifiy me of replies" box. If you're seeing the sheet properties window, then try selecting the commandbutton once more. And make sure you're still in design mode. If all fails, you could resort to a line of code: Make sure that the worksheet with the commandbutton is active. Open the VBE by hitting alt-F11 hit ctrl-g to see the immediate window Type this and hit enter: activesheet.commandbutton1.caption = "hi there" or if you want a linefeed in that caption: activesheet.commandbutton1.caption = "hi" & vblf & "there" Then back to excel to check. (Change Commandbutton1 to the name that you see in the namebox when that commandbutton is selected.) pickytweety wrote: I tried clicking the properties box already to look for the "caption" you mentioned. But it brings up the Sheet2 Properties, not the button object properties. I'm going to contact somebody from our IS group and see if our build restricts objects somehow. Will you do me a favor? Post a phony question out there, and checkmark the "notify me of replies box". Then answer your own question/posting and see if you get an email. I haven't been getting my response emails. The only way I know you answered is if I refresh my browser. -- Thanks, PTweety "Dave Peterson" wrote: I don't know why you're not seeing the options... But there's a Properties button on that same control toolbox toolbar. You can use the alternate suggestion, still. pickytweety wrote: I'm in design mode (the icon with the ruler, triangle and pencil is orange instead of blue). I right click on the CommandButton1 and nothing happens. Well actually I should say no menu pops up. I do get 8 sizing bubbles, 1 green spinner bubble, and my cursor changes to the normal white arrow pointing to the center of 4 tiny black arrows pointing outward. I'm wondering at this point if I have a setting somewhere that restricts objects somehow. I looked through Excel Options Advanced and All is checked for showing objects. I guess I can live with the button reading CommandButton1, but if you have any other ideas, let me know. -- Thanks, PTweety "Dave Peterson" wrote: Show that control toolbox toolbar again. Click on the design mode icon on that toolbar. Rightclick on the commandbutton and choose Command Object and then choose Edit. You could also change the caption by going into design mode and showing the commandbutton's property window. Look for the Caption property and change it to what you want. Private sub's won't show up in the alt-f8 dialog (same as the ribbon stuff you wrote about). It's a good way to hide them from users. But you can find the commandbutton on the worksheet and know what worksheet module it's in, right? pickytweety wrote: Hi Dave, The button is on a worksheet called Locations. What's on my toolbar (QAT) is the button-drawing icon. (I didn't realize there were even two button icons for drawing buttons in Excel--one for Forms Control and one for Control Toolbox.) So your message indicating there were two was really helpful. When I changed the code as you suggested my original problem was solved--THANKS!!! Now I have a new question--I want my button in the Excel worksheet to read "Prepare Report" instead of "CommandButton1". I tried right-clicking on it and various other things like I used to do, but can't figure out how to change the text. Also, from Excel I used to be able to click on the Developer tab, then Macros to see a list of all macros in the file. Is it not there now because I changed the "Sub" line to "Private Sub"? I do want the macro to show up under a list of macros in Excel. The code is sort of "hidden" in the VB Project Explorer now--especially if you forget which sheet it's on. I liked having it in the Module in VB Project Explorer. -- Thanks, PTweety "Dave Peterson" wrote: I'm confuse about where this button is--and what type it is. If it's on a worksheet (button replaced with a commandbutton), then just doubleclick on that commandbutton and you'll see where to place the code. If it's really on a toolbar, then the answer changes to something else. I'd build and assign the macro in code. pickytweety wrote: It was the form control button I was using, I think. So let me see if I understand....I need to go into Excel Options, Customize, delete the "Button(Form Control)" from my tool bar, add the "Command Button (Active X Control)" to my toolbar. Go back into the file, redraw my button. Revise the code as you suggested. Then how do I "attach" the macro to the button? I used to just right click the button and there would be a menu item called Assign Macro. -- Thanks, PTweety "Dave Peterson" wrote: I'm not quite sure what you're doing, but it sounds like the macro is assigned to a button from the Forms toolbar--and excel thinks that the macro should be in that other workbook. There are differences between the buttons from the Forms toolbar and commandbuttons from the Control toolbox toolbar. One of the nice (in this case for you) is that the macro isn't assigned to the commandbutton. The code is placed into the worksheet module that owns the commandbutton--and the procedure would be named something like: Private Sub CommandButton1_Click() This code will always be "assigned" to that button in that sheet. And if you move/copy the sheet, then the new sheet will have all the properties of the old sheet--including a copy of its own macro. But your code will have to change. One of the biggest differences is that all those unqualified ranges will refer to the object owning the code--in this case, the worksheet with the commandbutton. When the code is in a general module, the unqualified ranges will refer to the activesheet. I didn't test this very much (er, at all--but it did compile): Option Explicit Private Sub CommandButton1_Click() Dim wksLoc As Worksheet Dim wksTemp As Worksheet Dim wksNew As Worksheet Dim wksRight As Worksheet Dim strLocation As Range Dim strLoop As Range Dim r As Range Dim Store As Variant 'string or what???? Set wksLoc = Sheets("Locations") Set wksTemp = Sheets("Template") Set wksRight = Sheets("Right") 'Select the list of stores on Locations sheet With wksLoc Set strLoop = .Range("a2", .Range("a2").End(xlDown)) End With 'Grab print range Sheets("Template").Activate ActiveSheet.Calculate Application.Goto reference:="print_area" Set r = Selection 'Loop through each location (store) For Each strLocation In strLoop With wksTemp .Range("A5").Value = strLocation Store = .Range("a5").Value End With 'Create new sheet for location (store) wksTemp.Copy Befo=wksRight Set wksNew = ActiveSheet With wksNew 'Make print range ActiveSheet.PageSetup.PrintArea = r.Address 'Name new worksheet and calc it .Name = Trim(Store) ActiveSheet.Calculate 'Replace formulas with Values .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With Next strLocation 'We now have store sheets inbetween sheets Left and Right, but we need to 'move the Dist Total ' and the Total Company sheet in. With Worksheets("Dist Ttl") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "District Total" End With With Worksheets("District total") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With With Worksheets("Ttl Co") .Calculate .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 .Copy After:=Sheets("Left") ActiveSheet.Name = "Total Company" End With With Worksheets("Total company") .Cells.Copy .Cells.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End With End Sub pickytweety wrote: Hi, this macro was running fine, until the conversion to Office 2007. In the Locations worksheet I have a list of locations in Zone 1. A worksheet is created for each store in Zone 1. I manually move the created worksheets into another Book. Then I go back to the original file to run Zone 2. Only now I'm getting an error: Cannot run the macro 'Book 2.xlsx'!PrepareReport'. The macro may not be available in this workbook. Why is it looking to the new book for the macro? The macro is in the original file. It does let me click on the Developer tab, Macros, Run to run the macro, but why can't I just click the button anymore? Correction, why can't I click the button a second time (it runs the first time but then somehow gets hooked to the new file, so I can't click the button a second time). I've posted code below in case it helps. -- Thanks, PTweety Sub PrepareReport() ' -- Dave Peterson |
| All times are GMT +1. The time now is 08:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com