Ponderings of workbooks, macros, and buttons (oh my!)

[swampgirl]

I need a sounding board & any feedback y'all would care to share...

I've developed a workbook that will do exactly what I want it to do. The problems I'm running into are sharing the workbook with other users & saving copies with different file names while still keeping the functionality of one key macro & its custom button. I saw another post on here about saving my macro coding to a module - I think I've done that, but have no idea if I'm correct - I assume I'm not, because when I save the file under a different name & re-open it, the macro/button won't work without calling up another workbook.

Once they're working, I want these workbooks to live on a server, so I'm wondering if I should save a workbook with the macros to that server somewhere, then always have the custom button point to its macros. However - will things go wonky if more than one user is attempting to call up the macro at the same time?

My other option is to go to each user's computer and save the macro to their personal macro file - there are only four, so that wouldn't be too horrendous, but I don't think anyone else here will be able to do this should the need arise one day after I'm gone. (I'm the resident excel genius & you can see I'm obviously no such thing!)

Any thoughts on this? I've searched other postings and have seen many that come close to the same scenario I'm describing, but nothing that quite hits the nail on the head.

Recap:
Multiple workbooks that use the same macro/custom button.
Multiple users (not many) who will be accessing workbooks from a server.
Need to be able to keep a clean & empty original while saving new versions of the workbooks with different file names (that will still need to call up the macro).

I appreciate your time if you've made it through my rambling. I'm not above offering some kick-*** homemade brownies to anyone who can help and ever makes it to my neck of the woods!

 

[sweater_vests_rock]

swamp.

i'm sure there are cleaner solutions, but what we do at my company is simply save the macro source file (file holding the macro called by your custom menu button) to a shared network drive as an add-in. this way, it is accessible to any user and to multiple users (opens read-only in this case) via their excel toolbar from any excel document.

hope this helps. ben.

ps. welcome to the board!

 

[Smitty]

Welcome to the Board!

Another thing may also be that you're referring to the macro in your code with a specific filename reference. Most times you can substitute "ActiveWorkbook" to eliminate the error.

Can you post the code in question? As well as any routine in which you call it?

Smitty

 

[swampgirl]

hi there ben & smitty....i appreciate your time....

i've been thinking of doing what you mentioned, ben. i've just been worried about any problems that would arise if more than one person had a workbook open calling that macro & also what sort of confusion there would be from the users when another workbook opened. do you have any problems with those things happening?

i tried looking through my code to see if it was calling that workbook, but i didn't see anything. i've got about forty different trial workbooks and macros roaming around though, so that may have something to do with it. here is the code from one where i tried to put the code created from macro recorder into a module. another macro is mentioned at the beginning - i haven't had any problems with it running from any workbook. of course, i got the coding from another helpful member of this board & didn't write it myself, so that's probably why it works!

Public Sub ChangeOrderGenerator()

Application.Run "CopyActiveSheetToEndOfWorkbook"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H5").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("H6").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-1"
Range("H6").Select
Selection.Copy
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H6").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=15
Range("J44").Select
ActiveCell.FormulaR1C1 = "=RC[-4]+R[-1]C[-4]"
Range("J44").Select
Selection.Copy
Range("F44").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J44").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A19:F35").Select
Selection.ClearContents
Range("F19").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*RC[-1]"
Range("F19").Select
Selection.Copy
Range("F20:F35").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-12
Range("A20").Select
ActiveWindow.SmallScroll Down:=3
End Sub


i used to think i was good with computers. this thing is quickly causing me to question my skillz, though.

thanks y'all!!!!

 

[sweater_vests_rock]

swamp.

the nice thing about saving your workbook containing the macro as an add-in (.xla) rather than a workbook (.xls) is it opens "invisibly." the user will be prompted to enable macros as if (s)he were opening a workbook, but no other workbook will appear to open (you can see the add-in in the VBEditor, however).

does that make sense?
ben.

 

[Smitty]

Been out for a while, sorry...Not tested, but see if this is a bit quicker:

<font face=tahoma><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ChangeOrderGenerator()
    <SPAN style="color:#007F00">'   This may be where your problem is</SPAN>
    Application.Run "CopyActiveSheetToEndOfWorkbook"
    <SPAN style="color:#007F00">'   No need for all the selecting</SPAN>
        Range("E5") = <SPAN style="color:#00007F">Date</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Range("H6")
            .FormulaR1C1 = "=RC[-2]-1"
            .Copy
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        Range("F6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        <SPAN style="color:#00007F">With</SPAN> Range("J44")
            .FormulaR1C1 = "=RC[-4]+R[-1]C[-4]"
            .Copy
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        Range("F44").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("J44").ClearContents
        Range("A19:F35").ClearContents
        <SPAN style="color:#00007F">With</SPAN> Range("F19")
            .FormulaR1C1 = "=RC[-5]*RC[-1]"
            .Copy Range("F20:F35")
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

 

[Thommie]

I am having some of the same problems as Swampgirl.

I have a timesheet program that uses 5 basic macros. The process is for the user to open the last "timesheet maker" to create the new "timesheet maker". I needed to make this as user friendly as possible. I placed the macros in the personal workbook because the user will need to run them in order to make the new timesheets. I then assigned buttons for each one. The problem is that my macros are linked to a source document (i can't stand this!). ONe of the macros is to convert formulas into values. The problem is that when the user clicks the assigned button the macro converts formulas into values for the source document and any open documents. I can have the user make sure all other docs are closed, but I can't have it converting the formulas in the source doc too!! What has happened is that we no longer had a source document with formulas and I had to recreate the whole thing.

The fix I have now is for the user to access the macro when she is in the workbook (tools-macros-this workbook) and just bypass personal macros, unfortunately this means she can't use a button.

Here is the code...I tried to write the code to just do "Thisworkbook", but that didn't work. Is there anything i can do?


Sub ConvertFormulas()
'
' ConvertFormulas Macro
' Macro recorded 9/1/2005 by
'
' Keyboard Shortcut: Ctrl+Shift+V
'

For i = 5 To ActiveWorkbook.Sheets.Count
Sheets(i).Select

Range("C6:E11").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("P6:E8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B14").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Next i
End Sub[/code]

Thanks for any help!!!

 

[sweater_vests_rock]

thommie.

i'm surprised the code is overwriting all of your workbooks. have you tried making your ranges more specific? i think somethink like the following would work:

Sub ConvertFormulas_UPDATED()

   With ActiveWorkbook
     
      For i = 5 to .Sheets.Count
      
         With .Sheets(i)
            .Range("C6:E11").Copy
            .Range("C6:E11").PasteSpecial Paste:=xlPasteValues
            .Range("P6:E8").Copy
            .Range("P6:E8").PasteSpecial Paste:=xlPasteValues
            .Range("B14").Copy
            .Range("B14").PasteSpecial Paste:=xlPasteValues
         End With
      
      Next i
  
   End With

End Sub

as you found out the hard way, there is no undo-ing your macros. please try this on a copy of your data.

cheers. ben.

 

[Thommie]

sorry for the late reply. I will give this a try! (rhyming not intended