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
1,907
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,907
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.
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,907
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.
 

ismii

Board Regular
Joined
Feb 5, 2015
Messages
150
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?
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,907
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.
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,907
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.
 

Maikurra

New Member
Joined
Oct 20, 2015
Messages
3
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
 

ismii

Board Regular
Joined
Feb 5, 2015
Messages
150
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,544
Messages
5,445,099
Members
405,316
Latest member
joaoamaro

This Week's Hot Topics

Top