Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: macro storage help saga!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again.

    Well I have emailed myself the template and here is what's happeneing.

    Background is I made a template, attached a toolbar to it with 5 macro buttons. Stored the macro VBA code in 'This Workbook', attached the code to the buttons, and the toolbar to the workbook, no worries.

    Here's what it's doing. Let's say I open the template, and create a document called 'test'. I save 'test', and close Excel. But later, I realise I don't need 'test', and delete it. So far so good.

    BUT the next time I use 'template' and use a macro button, I get the message 'test1 (or whatever).xls could not be found. It's like the last document the macros were used in HAS to be on the computer. If it's deleted, or in my case now if I email it out, the macros cannot be found without that last document they were used in. The toolbar is programmed to open and close upon the template opening and closing, but they just don't seem to stay with the template, they go to 'doc1 based on the template', and won't work at all if 'doc 1 based on the template is deleted or emailed out or whatever. I have to reassign the macros to 'this workbook' each time, as they become assigned to 'doc1 based on the template' and so on.

    I am totally stumped how these storage locations work. What is the BEST place for a macro that needs to be attached to a worksheet and mailed out as an attachment. Is it All open workbooks, Personal.xls, or this workbook. I have tried to use help for this, but unfortunately, it isn't any help at all for this.

    Feedback appreciated/needed!!!!

    Many thanks, Lauren

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A complete stab in the dark, if you set-up your template as Read-Only and were forced to save the new book as a differant name keep all intact?

    James

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks James, I'll try that but I have my doubts. I've noticed this sort of thing on other projects but never had to worry about fixing it cuz I was the only one using the program. Now they like the program and want other people at other schools to use it and I am freaking out! Everything works great, it just seems I've got the bloody thing stored in the wrong place or something. Any other advice much needed!!

    Thank you!

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tried the read only - no luck. When I look under assign macros it just shows the last doc the macro was used in (i.e. CWindowsdesktoptest.xls!MacroInQuestion )and the one I originally assigned to the button (ie just MacroInQuestion) is somewhere down the list and I have to keep reassigning it if 'test' is deleted . It doesn't matter where I put the macro, personal, this workbook or all open workbooks, this still happens.

    Grrr.............

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Lauren,

    Would you care to post your code? Maybe that would make it clear as to why it's not working.

    James

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Macros you Create and attach to a toolbar are assigned to that particular book
    what ever the name, and is stored in the
    .xlb file as such. Changing the name of the
    workbook does not change the reference for
    the Toolbar macros which are stored in this xlb file. (the positions of the toolbars by the
    way are stored in the .pip file).
    Best way around this is to dynamically create your tollbar @ startup THEN delete
    when the workbook closes.


    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi James, okay here it is.

    Just now I went to Explorer>Profiles>Application Data>Templates and opened the template that way THEN Right clicked on the toolbar, clicked my CutPaste Button to look at 'assign macro' and 'SOSE1.xls!SOSECutPaste' was assigned to the button (basically document1 based on the template) instead of the original CutPaste macro named below instead of the original macro. If I now delete SOSE1 and try to use the template as the base for another document, I can't use my cutpaste macro without reassigning it from SOSE1.xls!SOSECutPaste to just SOSECutPaste. This is the case with all the codes assigned to my buttons, aside from my AutoOpen and AutoClose which open the toolbar. (well, at least something works!)

    Here is code. I hate this I always do projects in Access which I feel quite clever in and had this one thrown at me in Excel which I haven't studied that much so am feeling so LAME with a lot of things that seem to me they should be simple!

    God help me!

    Sub SOSECutPaste()
    ' CutPaste Macro
    ' Macro recorded 26/02/2002 by The Principal
    '

    '
    Dim shtOriginal As String
    Application.ScreenUpdating = False
    shtOriginal = ActiveSheet.Name
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    Sheets("My Class").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    With Selection.Font
    .Name = "Arial"
    .Size = 11
    .ColorIndex = 0
    End With
    Selection.Columns("D:AF").Select
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Selection.Columns("A:A").Select
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Selection.Columns("A:A").Select
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    ActiveCell.Offset(0, -3).Range("A1").Select
    Sheets(shtOriginal).Select
    ActiveCell.Offset(1, 1).Activate
    Application.ScreenUpdating = True

    End p.s. seeing that address of yours makes me homesick! Am from So Cal and have been living in Australia for 5 years - missing the good old US of A!

    It seems to me the problem maybe isn't so much in the code as what I've done to get it attached to the button (although that is all pretty straightforward, ain't it?)

    See any problems???

    Thanks mate(s)!

Some videos you may like

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
  •