Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-25-2015, 10:37 PM
troels troels is offline Path issue using a macro on the ribbon Windows 7 64bit Path issue using a macro on the ribbon Office 2010 64bit
Novice
Path issue using a macro on the ribbon
 
Join Date: May 2015
Posts: 1
troels is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 05-26-2015, 10:25 AM
charlesdh charlesdh is offline Path issue using a macro on the ribbon Windows 7 32bit Path issue using a macro on the ribbon Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Path issue using a macro on the ribbon 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft