Making toolbar with macros work in different workbooks

R

Rach

New Member
Joined
Apr 11, 2002
Messages
2
Help!!!!
I have created a simple program to act as a mark book. A teacher in my school wants a copy. As part of my mark book I have created a custom toolbar which runs some macros to protect sheets, delete children etc. It works fine in my copy (called SARK)

I created another copy (called SARK9899) for the other teacher the menu with the macros on, when you click to use one in the SARK9899, doesn't work - it tries to load the other version(SARK) and use them(the macro you select) in that(SARK) rather than the version you have loaded(SARK9899).

If I re-assign the macro buttons in SARK9899 thus gettting them to work, when I load up my version(SARK), they then do not work in mine...same problem the other way round.

However, if I use the short cut keys they will work in either work book...but I want them to work for a toolbar.(Sulky face, stamping, banging of head and eating of antother choc bar!) I don't want to put buttons into the worksheets because there are a lot of sheets and it would look messy....

Please help
This message was edited by Rach on 2002-04-12 10:22
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
On 2002-04-12 10:19, Rach wrote:
As part of my mark book I have created a custom toolbar whcich runs some macros to protect sheets, delete children etc
Click to expand...

That's some macro :). What exactly is the code for the macros and where are they stored (ie the SARK workbook or your personal macro workbook)?
 
Upvote 0
not sure where they are stored (I'm not really any kind of expert and am learning as I go along...hence loads of confusion.) if I go into visual basic editor, in the project window the modules, user forms and objects where the macros are are under
VBA project (SARK.xls)
or VBA project (SARK9899.xls.....if this is the wrong thing, where should I look (I'm not really a cretin....honest)

Code...there's a lot....

Code for Macros I pasted this into word…any use????? (I inserted the MODULE 13 etc)

MODULE 13

Sub deletechildassesssheet()
'
' deletechildassesssheet Macro
' Macro recorded 09/12/2001 by Richard & Rachel
'

'
ActiveWindow.LargeScroll Down:=1
Selection.Interior.ColorIndex = 11
Selection.Font.ColorIndex = 11
Selection.EntireRow.Hidden = True
End Sub
Sub deletchildsummary()
'
' deletchildsummary Macro
' Macro recorded 09/12/2001 by Richard & Rachel
'
' Keyboard Shortcut: Ctrl+a
'
Selection.ClearContents
Selection.Interior.ColorIndex = 11
Selection.Font.ColorIndex = 11
Selection.EntireRow.Hidden = True
End Sub


MODULE 15

Sub MTSC1T1()

End Sub
Sub MTPSC1T1()
'
' MTPSC1T1 Macro
' Macro recorded 10/04/2002 by Richard & Rachel
'

'
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("science planning computerised4.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "SC1 Level 2"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
Range("A2:F2").Select
Range("F2").Activate
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
Selection.Replace What:="I can", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveWindow.SmallScroll Down:=0
ActiveWindow.WindowState = xlMinimized
Windows("SARK9899.xls").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
Sub MTPSC1T2()
'
' MTPSC1T2 Macro
' Macro recorded 10/04/2002 by Richard & Rachel
'

'
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("science planning computerised4.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveWindow.SmallScroll Down:=8
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=7
Range("A11").Select
ActiveSheet.Paste
Range("A11:F11").Select
Range("F11").Activate
Application.CutCopyMode = False
Selection.Copy
Range("A12").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
ActiveWindow.SmallScroll Down:=5
ActiveWindow.WindowState = xlMinimized
Windows("SARK9899.xls").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
Sub MTPKT1()
'
' MTPKT1 Macro
' Macro recorded 10/04/2002 by Richard & Rachel
'

'
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("science planning computerised4.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("A19").Select
ActiveSheet.Paste
Range("A19:F19").Select
Range("F19").Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=6
Range("A20").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
ActiveWindow.SmallScroll Down:=6
ActiveWindow.WindowState = xlMinimized
Windows("SARK9899.xls").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
Sub MTPKT2()
'
' MTPKT2 Macro
' Macro recorded 10/04/2002 by Richard & Rachel
'

'
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("science planning computerised4.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1
ActiveWindow.LargeScroll Down:=5
Range("A27").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=4
Range("A27:F27").Select
Range("F27").Activate
Application.CutCopyMode = False
Selection.Copy
Range("A28").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
Selection.Replace What:="I can", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveWindow.WindowState = xlMinimized
Windows("SARK9899.xls").Activate
ActiveWindow.WindowState = xlMaximized
End Sub

Sub Box()
Load UserForm1
UserForm1.Show
End Sub

MODULE 2

Sub unprotectsheet()
'
' unprotectsheet Macro
' Macro recorded 21/06/2001 by Richard & Rachel
'
' Keyboard Shortcut: Ctrl+u
'
ActiveSheet.Unprotect
End Sub
Sub protectsheet()
'
' protectsheet Macro
' Macro recorded 21/06/2001 by Richard & Rachel
'
' Keyboard Shortcut: Ctrl+p
'
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Sub protectall()
'
' protectall Macro
' Macro recorded 21/06/2001 by Richard & Rachel
'
' Keyboard Shortcut: Ctrl+m
'
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC1 Level 1").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC4 Level 4").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC4 Level 3").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC4 Level 2").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC4 Level 1").Select
Application.Run "'SARK9899.xls'!protectsheet"
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SC3 Level 5").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC3 Level 4").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC3 Level 3").Select
Application.Run "'SARK9899.xls'!protectsheet"
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SC3 Level 2").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC3 Level 1").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC2 Level 5").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("Sc2 Level 4").Select
Application.Run "'SARK9899.xls'!protectsheet"
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SC2 Level 3").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC2 Level 2").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC2 Level 1").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC1 level 5").Select
Application.Run "'SARK9899.xls'!protectsheet"
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("SC1 level 4").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC1 Level 3").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC1 Level 2").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC1 Level 1").Select
Application.Run "'SARK9899.xls'!protectsheet"
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SC1 Level 6").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC2 Level 6").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC3 Level 6").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("SC4 Level 6").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("% of children at each level").Select
Application.Run "'SARK9899.xls'!protectsheet"
Sheets("% of children at each level a,b").Select
Application.Run "'SARK9899.xls'!protectsheet"


End Sub
Sub unprotectall()
'
' unprotectall Macro
' Macro recorded 21/06/2001 by Richard & Rachel
'
' Keyboard Shortcut: Ctrl+n
'
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC1 Level 2").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC1 Level 3").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC1 level 4").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC1 level 5").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC1 level 6").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC2 Level 1").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC2 Level 2").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC2 Level 3").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("Sc2 Level 4").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC2 Level 5").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC2 Level 6").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC3 Level 1").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC3 Level 2").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC3 Level 3").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC3 Level 4").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC3 Level 5").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC3 Level 6").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC4 Level 1").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC4 Level 2").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC4 Level 3").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC4 Level 4").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC4 Level 5").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC1 Level 1").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("SC4 Level 6").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("% of children at each level").Select
Application.Run "'SARK9899.xls'!unprotectsheet"
Sheets("% of children at each level a,b").Select
Application.Run "'SARK9899.xls'!unprotectsheet"


End Sub

ALSO, USER FORM ONE CODE….for MTPSC1T..T2….KT1…KT2

Private Sub CommandButton3_Click()
MTPKT1
UserForm1.Hide
End Sub

Private Sub CommandButton4_Click()
MTPKT2
UserForm1.Hide
End Sub


Private Sub CommandButton5_Click()
End
End Sub

Private Sub Image1_Click()

End Sub

Private Sub UserForm_Click()

End Sub
This message was edited by Rach on 2002-04-12 10:43
This message was edited by Rach on 2002-04-12 10:44
 
Upvote 0
Sorry, Rach, I can't think why this would work in one workbook and not the other. The only things you might want to check (although I'm sure you have) are that all the modules and code are in both workbooks (open them separately and check in the VBA editor) and that all the code in, say, the SARK.xls workbook explicitly refers to the sheets within that workbook and to the workbook name itself.

From the looks of it, you've recorded the macro yourself and have a lot of extraneous code which could be optimised to make things simpler. No promises as I might be busy this weekend, but if you could send copies of both workbooks to the E-Mail address below I could take a look, clean up your code and let you have them back some time on Sunday. Delete any data on the sheets that you want kept private but leave some sample data in there for me to work with. Sorry I can't be of more help at the minute, but the pub is calling :).

chris@mudface.freeserve.co.uk
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macros for Quick Access Toolbar - Where to Save Macros
Replies
1
Views
267
6StringJazzer
6StringJazzer
M
  • Question
VBA help required in creating multiple workbooks from one workbook.
Replies
5
Views
270
jolivanes
jolivanes
zookeepertx
  • Question
Macro will run correctly in 1 workbook but not another
Replies
5
Views
478
zookeepertx
zookeepertx
C
  • Question
Running Data Refresh Macro, then Table Update Macro, Back-to-Back?
2
Replies
11
Views
525
Alex Blakenburg
A
A
  • Solved
Command buttons not working when on different workbook
Replies
3
Views
157
Alan_CT06
A

Forum statistics

Threads
1,214,632
Messages
6,120,652
Members
448,975
Latest member
sweeberry

Share this page

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