HI question on this macro run. I put vba code into module and add command or avtive button, But

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,072
Office Version
  1. 365
Platform
  1. Windows
So im using 2007 excel I have a sheet I put code in module and assign a button to it right. So I brought it home and I have 2010, but didn't work when I used the button, but if I go into visual basic and play it it runs fine. What am I doing wrong. Am I assigning it in a wrong way?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Ok I read this article. Whats funny is at work it works fine, but when im home I see that it opens another sheet in the back round for it to work. I just don't get it.
 
Upvote 0
Maybe instead of putting my codes in sheet 1 where I need it to run. Maybe put it into Workbook? If I put it in personal workbook in a module and assign it there works fine, but I need everyone to have access to it. Why im tryint to put it in sheet 1 or maybe workbook.
 
Upvote 0
If it works, that's good!

I would have to see the code to see if there's anything else going on, though. Maybe it looks for a specific sheet and if it doesn't exist, it creates it?
 
Upvote 0
Gonna do more testing ill get back to you thanks
 
Upvote 0
Active buttons work in 2007, but 2010 it doesnt i have to assgin again or i can run it in macros hit "run"

Ok after testing. This is what I cant explain why its not working.

So at work I use 2007 excel. I am using a sheet that has 3 tabs on the main sheet im using. I made 3 macro active buttons to run 3 marcos I want to use on one of the sheets. The codes are in the worksheet called "data"
After im done with my sheet for the day I use a macro to save 2 of my tabs in a folder. It takes sheet "data" and "totals" and saves it to a workbook called timesheet. When I open it on 2007 at work the "data" sheet I have 3 buttons I can use and the code is in visual basic under "data" sheet. This is because I need the code to save also with the sheets so I can use macro's. So I use the macro's here on 2007 no problems, but if I do the same thing at home on 2010. When I try and run the 3 buttons after its saved there. I get 400x red error and or it looks for the orginal main sheet to and opens it so the macro's will work. I don't get that here on 2007 and I checked in visual basic and I see my 1 sheet in there. I see no back up sheets so macros can work. Though in 2010 I can run my macros, but I have to go to marcos and run it. I cant use buttons I just don't know why, but if I right click the button and assign code in visual then the button will work.

So why am I having this issue on 2010 and not on 2007. My easy way out would be to run it off my personal book macros book that works, but since so many people use this I need code in the sheet as its saved so everyone can use it.

Anybody know why this may be happening? Thanks

https://www.dropbox.com/s/ca82ofm8tq33vox/image test 2.xlsm?dl=0 <--- sheet with3 buttons. 2010 I cant use the 3 buttons it wont look for code under the "data" sheet. ill get error, but if I just un it under macro's and click run works or I right click it and assign it again it works.
 
Upvote 0
Re: Active buttons work in 2007, but 2010 it doesnt i have to assgin again or i can run it in macros hit "run"

Just tested it on 2016 same problem. Maybe the way its saving the 2 tabs, but I recorded the saveas enabled sheet 2007 and 2010 and it the same code.
 
Upvote 0
Re: Active buttons work in 2007, but 2010 it doesnt i have to assgin again or i can run it in macros hit "run"

Not sure if this is an Office version problem but it may have coincided with my upgrade from 2010 to 2016? If I run my macro in VB it works fine. If I run it via my macro button in the "Dashboard" worksheet it runs but don't get the same result. Strangely, the start and the end of the code is fine but it has a problem with only some parts. The code in blue is what is not happening if I run the macro from the button in the worksheet. As I said, works fine if I Run or Step-into via VB. Also, I'm a new, self-taught hack at VBA so apologies in advance for the inefficient code writing! Any help would be much appreciated.


Sub Dashboard_Refresh_Data()
'
' Dashboard_Refresh_Data Macro
'
' Clear old Contracts data from report
Worksheets("Dashboard").Range("A59:I66").ClearContents

' Copy required data from the Contracts tab and paste it into the report
Application.ScreenUpdating = False
Sheets("Contracts").Select

Dim x As Long
For x = 2 To 1000
If Cells(x, "Q") = "OPEN" Then
Cells(x, "D").Resize(, 2).Copy
Worksheets("Dashboard").Range("A66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "H").Resize(, 2).Copy
Worksheets("Dashboard").Range("C66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "O").Resize(, 1).Copy
Worksheets("Dashboard").Range("F66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "M").Resize(, 1).Copy
Worksheets("Dashboard").Range("G66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "R").Resize(, 1).Copy
Worksheets("Dashboard").Range("I66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next x



' Clear old AR data from report
Worksheets("Dashboard").Range("A76:I88").ClearContents

' Copy required data from the Tickets tab and paste it into the report


Worksheets("Tickets").Select

Dim y As Long
For y = 2 To 10000
If Cells(y, "AG") = "N" Then
Cells(y, "B").Resize(, 1).Copy
Worksheets("Dashboard").Range("A88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "F").Resize(, 1).Copy
Worksheets("Dashboard").Range("B88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "D").Resize(, 1).Copy
Worksheets("Dashboard").Range("C88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "M").Resize(, 1).Copy
Worksheets("Dashboard").Range("D88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "AD").Resize(, 1).Copy
Worksheets("Dashboard").Range("G88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "AH").Resize(, 1).Copy
Worksheets("Dashboard").Range("I88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "AJ").Resize(, 1).Copy
Worksheets("Dashboard").Range("F88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next y

'Delete old data from Appendix Summary
Sheets("Appendix").Activate
ActiveSheet.Range("A4", Range("A4").SpecialCells(xlLastCell)).ClearContents




' Copy required data from the Contracts tab and paste it into the Appendix Contracts Summary report


Worksheets("Contracts").Range("CONTRACTS[Contract_Date]:CONTRACTS[Cont_Balance]").Copy
Worksheets("Appendix").Range("A4").PasteSpecial xlPasteValues


Worksheets("Contracts").Range("CONTRACTS[Contract_Status]").Copy
Worksheets("Appendix").Range("P4").PasteSpecial xlPasteValues


Worksheets("Contracts").Range("CONTRACTS[Payment_Terms]").Copy
Worksheets("Appendix").Range("Q4").PasteSpecial xlPasteValues


Worksheets("Contracts").Range("CONTRACTS[On Farm Price]").Copy
Worksheets("Appendix").Range("R4").PasteSpecial xlPasteValues


Application.CutCopyMode = False


Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Re: Active buttons work in 2007, but 2010 it doesnt i have to assgin again or i can run it in macros hit "run"

I would try to move the code from the sheets into a module and link the buttons to them there.
I don't see anything specifying which sheet to pull from in the code (just using the active sheet), but the buttons are linked to "Sheet1".

I can play around with it some this weekend.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,789
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top