#1
|
|||
|
|||
Path issue using a macro on the ribbon
I have created several macros which I use every day. I have added these to the ribbon as buttons and they work fine.
The spreadsheet is for daily finances and I typically save a new copy each day with the current date. So say I add the macros in a file called May-26.xlsm and tomorrow I save this as May-27.xlsm. The problem is that when I try to invoke the macros in May-27.xlsm it has stored the name of the original spreadsheet for each button in the ribbon, so it loads May-26.xlsm in the background (it is visible). I have tried storing the macros in my personal.xlsb file in my profile and if I do this they simply do not run from the ribbon. I can run the macros no problem using shortcuts or from the Macro command. I can even add buttons to the Quick Launch toolbar and these work correctly without loading the original spreadsheet. The issue appears to be that the act of adding the macros to the ribbon as buttons stores a hard-coded link to the name of the current spreadsheet along with the macro and that subsequently renaming the spreadsheet does not update this hard-coding. Is there a way to prevent this and make the ribbon commands refer to macros in the current spreadsheet? |
#2
|
|||
|
|||
Hi,
Why not make a stand-alone file for the Macro and then have the code to allow you to open the desired workbook for you too update. You can then do a save as for the workbook. In the files that you are updating you do not need a macro. In the code below you can see what I did that allow the user to ope multiple workbooks to update. This may help you. Code:
Option Explicit Public Mystore As String Public MYdate As String Public Mysales As String Sub Gather_Data() Application.ScreenUpdating = False Application.EnableEvents = False Dim s Dim Store As Variant ''' store nr Dim Sdate As Variant ''' sales date Dim Dwb As Workbook '' for daily balance Dim LFwb As Workbook Dim SSwb As Workbook Dim fd As FileDialog Dim Myfile As String Dim Mypath As String Dim lngCount As Long Dim mysrow As Long Dim mydcol As Long Dim Rlrow As Long 'Create a FileDialog object as a File Picker dialog box. Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Declare a variable to contain the path Mypath = ThisWorkbook.Path 'Use a With...End With block to reference the FileDialog object. With fd 'Use the Show method to display the File Picker dialog box and return the user's action. 'The user pressed the action button. '''''''''''''''''''''''' .AllowMultiSelect = True If .Show = -1 Then For lngCount = 1 To .SelectedItems.Count Myfile = .SelectedItems(lngCount) Workbooks.Open (Myfile) If InStr(ActiveWorkbook.Name, "SALES") <> 0 Then Set SSwb = ActiveWorkbook ElseIf InStr(ActiveWorkbook.Name, "Long") <> 0 Then Set LFwb = ActiveWorkbook ElseIf InStr(ActiveWorkbook.Name, "Daily") <> 0 Then Set Dwb = ActiveWorkbook '''' Daily workbook End If Next lngCount 'The user pressed Cancel. Else Exit Sub End If End With 'Set the object variable to Nothing. Unloads from memory Set fd = Nothing '''' Open Daily Balance workbook''' Dwb.Activate ''' Get the Store Nr. and Date '' Mysales = ActiveSheet.Range("C3") MYdate = ActiveSheet.Range("G1") Mystore = ActiveSheet.Range("C1") '''' Activate Sales ''' SSwb.Activate ''' Find Store and date to populate the data '' With ActiveSheet.UsedRange Set Sdate = .Find(MYdate, lookat:=xlPart) ''' find date to get the row nr and column If Not Sdate Is Nothing Then ''' Date found '''' mydcol = Sdate.Column End If End With Rlrow = ActiveSheet.Range("A" & Sdate.Row + 1).End(xlDown).Row With Range("A" & Sdate.Row + 1 & ":" & "A" & Rlrow) '''' Find the Store and then the date ''' Set Store = .Find(Mystore, lookat:=xlPart) If Not Store Is Nothing Then '''' Store found ''' mysrow = Store.Row ''' Populate the Data ActiveSheet.Cells(mysrow, mydcol).Value = Mysales End If End With LFwb.Activate ''' open the "Long Form '''' With ActiveSheet.UsedRange '''' Find the Store and then the date ''' Set Store = .Find(Mystore, lookat:=xlPart) If Not Store Is Nothing Then '''' Store found Populate the data''' ActiveSheet.Cells(Store.Row, 2).Value = Mysales End If End With End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Ribbon issue | kmac | Word | 3 | 01-09-2015 04:05 PM |
Is it possible to add a custom macro button to the FILE tab of the ribbon in excel | sbapabck | Excel | 2 | 04-11-2014 07:36 AM |
How to import the customized ribbon to Word without overwriting the existing Ribbon? | SharonSh | Word VBA | 0 | 09-26-2013 11:47 PM |
Excel 2010 Ribbon look like 2007's Ribbon | esotop | Excel | 0 | 03-22-2011 07:05 PM |