Making toolbar with macros work in different workbooks

[Rach]

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 ]

[Mudface]

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

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)?

[Rach]

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 ]

[Mudface]

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

[nisht]

Friend,

I have a workbook,, 'Result Sheet' specially programmed for teachers.. you can see that file and can improve your cod..
if you have suggestion you are always welcome

file nos is 9

http://www.pexcel.com/download.htm

nishith desai
http://www.pexcel.com