Making toolbar with macros work in different workbooks
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Making toolbar with macros work in different workbooks

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

    ni****h desai
    http://www.pexcel.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com