Problem w/custom Macro Buttons on move to Laptop

In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
clicked executed the VBA code in my personal.xls file.  The excel.xlb was
located in c:\Documents and Setting\comp1\Application Data\Excel and the
personal.xls file was located in "c:\Documents and Setting\comp1\Application
Data\Excel\Xlstart".

On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
the personnal.xls file into c:\Documents and Setting\Laptop\Application
Data\Excel\Xlstart

On the laptop when I bring Excel up the custom macro buttons are there
and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).

However when I click on a custom macro button I get the following error:
"A document with the name personal.xls is already open.  You cannot open two
documents with the same name even if the documents are in different folders."

I did a search on the whole disk and there is only one file named personal.xls
and it is located as above.

If I click on Tools->Customize->right click the macro icon->assign Macro.  The
Assign Macro Window appears with the macro name c:\Documents and
Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro

If I reassign the custom button to that same macro that shows up in the window
list below the Macro Name which shows the name Personal.XLS!mymacro, than that
custom macro button works fine after I close and reopen Excel while the other
custom buttons still won't work.

Since I have about 20 custom buttons assigned to 20 different VBA macros it
would be cumbersome to reassign each button.

Is there an easier way to redirect all the custom macro buttons to
Personal.XLS!? 

Thanks for any help.

Dennis
0
Dennis8944 (79)
7/17/2005 7:29:07 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
351 Views

Similar Articles

[PageSpeed] 39

First, back up files first!

But this seemed to work ok for me:

Option Explicit
Sub testme01()

    Dim cBar As CommandBar
    Dim cBarName As String
    Dim ctrl As CommandBarControl
    Dim newWkbk As Workbook
    Dim newWkbkName As String
    Dim ExclamePos As Long
    
    newWkbkName = "Personal.xls"
    cBarName = "yourtoolbarnamehere"
    
    Set newWkbk = Nothing
    On Error Resume Next
    Set newWkbk = Workbooks(newWkbkName)
    On Error GoTo 0    
    If newWkbk Is Nothing Then
        MsgBox "Please open the new " & newWkbkName & " file!"
        Exit Sub
    End If
    
    Set cBar = Nothing
    On Error GoTo 0
    Set cBar = Application.CommandBars(cBarName)
    On Error GoTo 0    
    If cBar Is Nothing Then
        MsgBox "Missing commandbar named: " & cBarName
        Exit Sub
    End If

    For Each ctrl In cBar.Controls
        If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
            Debug.Print "----" & ctrl.Caption & "----" & vbLf & ctrl.OnAction
            ExclamePos = InStr(1, ctrl.OnAction, "!")
            If ExclamePos > 0 Then
                ctrl.OnAction = newWkbk.Name & Mid(ctrl.OnAction, ExclamePos)
            End If
            Debug.Print ctrl.OnAction
        End If
    Next ctrl

End Sub

===========
But one way to avoid this problem is to build that toolbar when the workbook
opens (and delete it when the workbook closes.

This is how I do it:
http://groups.google.co.uk/groups?threadm=40E095F1.5CB35B41%40msn.com



Dennis@NoSpam.com wrote:
> 
> In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
> clicked executed the VBA code in my personal.xls file.  The excel.xlb was
> located in c:\Documents and Setting\comp1\Application Data\Excel and the
> personal.xls file was located in "c:\Documents and Setting\comp1\Application
> Data\Excel\Xlstart".
> 
> On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
> excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
> the personnal.xls file into c:\Documents and Setting\Laptop\Application
> Data\Excel\Xlstart
> 
> On the laptop when I bring Excel up the custom macro buttons are there
> and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).
> 
> However when I click on a custom macro button I get the following error:
> "A document with the name personal.xls is already open.  You cannot open two
> documents with the same name even if the documents are in different folders."
> 
> I did a search on the whole disk and there is only one file named personal.xls
> and it is located as above.
> 
> If I click on Tools->Customize->right click the macro icon->assign Macro.  The
> Assign Macro Window appears with the macro name c:\Documents and
> Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro
> 
> If I reassign the custom button to that same macro that shows up in the window
> list below the Macro Name which shows the name Personal.XLS!mymacro, than that
> custom macro button works fine after I close and reopen Excel while the other
> custom buttons still won't work.
> 
> Since I have about 20 custom buttons assigned to 20 different VBA macros it
> would be cumbersome to reassign each button.
> 
> Is there an easier way to redirect all the custom macro buttons to
> Personal.XLS!?
> 
> Thanks for any help.
> 
> Dennis

-- 

Dave Peterson
0
petersod (12005)
7/17/2005 10:33:26 PM
Thanks for the code Dave, it's neat!

Unfortunately my macro buttons are spread out over many different command bars
so it would be just as easy to reassign the 20 macros by leaving the
Tools->Customize window up and right click on each  macro icon and then assign
Macro to the Personal.xls!mymacro in the macro list box.

MS should have an easier way to do this.

Dennis

 
Dave Peterson <petersod@verizonXSPAM.net> wrote:

>First, back up files first!
>
>But this seemed to work ok for me:
>
>Option Explicit
>Sub testme01()
>
>    Dim cBar As CommandBar
>    Dim cBarName As String
>    Dim ctrl As CommandBarControl
>    Dim newWkbk As Workbook
>    Dim newWkbkName As String
>    Dim ExclamePos As Long
>    
>    newWkbkName = "Personal.xls"
>    cBarName = "yourtoolbarnamehere"
>    
>    Set newWkbk = Nothing
>    On Error Resume Next
>    Set newWkbk = Workbooks(newWkbkName)
>    On Error GoTo 0    
>    If newWkbk Is Nothing Then
>        MsgBox "Please open the new " & newWkbkName & " file!"
>        Exit Sub
>    End If
>    
>    Set cBar = Nothing
>    On Error GoTo 0
>    Set cBar = Application.CommandBars(cBarName)
>    On Error GoTo 0    
>    If cBar Is Nothing Then
>        MsgBox "Missing commandbar named: " & cBarName
>        Exit Sub
>    End If
>
>    For Each ctrl In cBar.Controls
>        If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
>            Debug.Print "----" & ctrl.Caption & "----" & vbLf & ctrl.OnAction
>            ExclamePos = InStr(1, ctrl.OnAction, "!")
>            If ExclamePos > 0 Then
>                ctrl.OnAction = newWkbk.Name & Mid(ctrl.OnAction, ExclamePos)
>            End If
>            Debug.Print ctrl.OnAction
>        End If
>    Next ctrl
>
>End Sub
>
>===========
>But one way to avoid this problem is to build that toolbar when the workbook
>opens (and delete it when the workbook closes.
>
>This is how I do it:
>http://groups.google.co.uk/groups?threadm=40E095F1.5CB35B41%40msn.com
>
>
>
>Dennis@NoSpam.com wrote:
>> 
>> In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
>> clicked executed the VBA code in my personal.xls file.  The excel.xlb was
>> located in c:\Documents and Setting\comp1\Application Data\Excel and the
>> personal.xls file was located in "c:\Documents and Setting\comp1\Application
>> Data\Excel\Xlstart".
>> 
>> On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
>> excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
>> the personnal.xls file into c:\Documents and Setting\Laptop\Application
>> Data\Excel\Xlstart
>> 
>> On the laptop when I bring Excel up the custom macro buttons are there
>> and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).
>> 
>> However when I click on a custom macro button I get the following error:
>> "A document with the name personal.xls is already open.  You cannot open two
>> documents with the same name even if the documents are in different folders."
>> 
>> I did a search on the whole disk and there is only one file named personal.xls
>> and it is located as above.
>> 
>> If I click on Tools->Customize->right click the macro icon->assign Macro.  The
>> Assign Macro Window appears with the macro name c:\Documents and
>> Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro
>> 
>> If I reassign the custom button to that same macro that shows up in the window
>> list below the Macro Name which shows the name Personal.XLS!mymacro, than that
>> custom macro button works fine after I close and reopen Excel while the other
>> custom buttons still won't work.
>> 
>> Since I have about 20 custom buttons assigned to 20 different VBA macros it
>> would be cumbersome to reassign each button.
>> 
>> Is there an easier way to redirect all the custom macro buttons to
>> Personal.XLS!?
>> 
>> Thanks for any help.
>> 
>> Dennis

0
Dennis8944 (79)
7/18/2005 3:45:18 PM
Maybe just cycling through all the commandbars would help:

Option Explicit
Sub testme01()

    Dim cBar As CommandBar
    Dim ctrl As CommandBarControl
    Dim newWkbk As Workbook
    Dim newWkbkName As String
    Dim ExclamePos As Long
    
    newWkbkName = "Personal.xls"
    
    Set newWkbk = Nothing
    On Error Resume Next
    Set newWkbk = Workbooks(newWkbkName)
    On Error GoTo 0
    If newWkbk Is Nothing Then
        MsgBox "Please open the new " & newWkbkName & " file!"
        Exit Sub
    End If

    For Each cBar In Application.CommandBars
        For Each ctrl In cBar.Controls
            If ctrl.BuiltIn Then
                'do nothing
            Else
                If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
                    Debug.Print "----" & ctrl.Caption & "----" & vbLf _
                                      & ctrl.OnAction
                    ExclamePos = InStr(1, ctrl.OnAction, "!")
                    If ExclamePos > 0 Then
                        ctrl.OnAction = newWkbk.Name _
                                      & Mid(ctrl.OnAction, ExclamePos)
                    End If
                    Debug.Print ctrl.OnAction
                End If
            End If
        Next ctrl
    Next cBar

End Sub

==========
But I think the easiest solution would be to create the toolbars on the fly. 
Then you won't have to go through the next time you upgrade.


Dennis@NoSpam.com wrote:
> 
> Thanks for the code Dave, it's neat!
> 
> Unfortunately my macro buttons are spread out over many different command bars
> so it would be just as easy to reassign the 20 macros by leaving the
> Tools->Customize window up and right click on each  macro icon and then assign
> Macro to the Personal.xls!mymacro in the macro list box.
> 
> MS should have an easier way to do this.
> 
> Dennis
> 
> 
> Dave Peterson <petersod@verizonXSPAM.net> wrote:
> 
> >First, back up files first!
> >
> >But this seemed to work ok for me:
> >
> >Option Explicit
> >Sub testme01()
> >
> >    Dim cBar As CommandBar
> >    Dim cBarName As String
> >    Dim ctrl As CommandBarControl
> >    Dim newWkbk As Workbook
> >    Dim newWkbkName As String
> >    Dim ExclamePos As Long
> >
> >    newWkbkName = "Personal.xls"
> >    cBarName = "yourtoolbarnamehere"
> >
> >    Set newWkbk = Nothing
> >    On Error Resume Next
> >    Set newWkbk = Workbooks(newWkbkName)
> >    On Error GoTo 0
> >    If newWkbk Is Nothing Then
> >        MsgBox "Please open the new " & newWkbkName & " file!"
> >        Exit Sub
> >    End If
> >
> >    Set cBar = Nothing
> >    On Error GoTo 0
> >    Set cBar = Application.CommandBars(cBarName)
> >    On Error GoTo 0
> >    If cBar Is Nothing Then
> >        MsgBox "Missing commandbar named: " & cBarName
> >        Exit Sub
> >    End If
> >
> >    For Each ctrl In cBar.Controls
> >        If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
> >            Debug.Print "----" & ctrl.Caption & "----" & vbLf & ctrl.OnAction
> >            ExclamePos = InStr(1, ctrl.OnAction, "!")
> >            If ExclamePos > 0 Then
> >                ctrl.OnAction = newWkbk.Name & Mid(ctrl.OnAction, ExclamePos)
> >            End If
> >            Debug.Print ctrl.OnAction
> >        End If
> >    Next ctrl
> >
> >End Sub
> >
> >===========
> >But one way to avoid this problem is to build that toolbar when the workbook
> >opens (and delete it when the workbook closes.
> >
> >This is how I do it:
> >http://groups.google.co.uk/groups?threadm=40E095F1.5CB35B41%40msn.com
> >
> >
> >
> >Dennis@NoSpam.com wrote:
> >>
> >> In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
> >> clicked executed the VBA code in my personal.xls file.  The excel.xlb was
> >> located in c:\Documents and Setting\comp1\Application Data\Excel and the
> >> personal.xls file was located in "c:\Documents and Setting\comp1\Application
> >> Data\Excel\Xlstart".
> >>
> >> On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
> >> excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
> >> the personnal.xls file into c:\Documents and Setting\Laptop\Application
> >> Data\Excel\Xlstart
> >>
> >> On the laptop when I bring Excel up the custom macro buttons are there
> >> and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).
> >>
> >> However when I click on a custom macro button I get the following error:
> >> "A document with the name personal.xls is already open.  You cannot open two
> >> documents with the same name even if the documents are in different folders."
> >>
> >> I did a search on the whole disk and there is only one file named personal.xls
> >> and it is located as above.
> >>
> >> If I click on Tools->Customize->right click the macro icon->assign Macro.  The
> >> Assign Macro Window appears with the macro name c:\Documents and
> >> Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro
> >>
> >> If I reassign the custom button to that same macro that shows up in the window
> >> list below the Macro Name which shows the name Personal.XLS!mymacro, than that
> >> custom macro button works fine after I close and reopen Excel while the other
> >> custom buttons still won't work.
> >>
> >> Since I have about 20 custom buttons assigned to 20 different VBA macros it
> >> would be cumbersome to reassign each button.
> >>
> >> Is there an easier way to redirect all the custom macro buttons to
> >> Personal.XLS!?
> >>
> >> Thanks for any help.
> >>
> >> Dennis

-- 

Dave Peterson
0
petersod (12005)
7/18/2005 6:01:17 PM
Thanks again Dave.  This code did it for all my macros.

Dennis

Dave Peterson <petersod@verizonXSPAM.net> wrote:

>Maybe just cycling through all the commandbars would help:
>
>Option Explicit
>Sub testme01()
>
>    Dim cBar As CommandBar
>    Dim ctrl As CommandBarControl
>    Dim newWkbk As Workbook
>    Dim newWkbkName As String
>    Dim ExclamePos As Long
>    
>    newWkbkName = "Personal.xls"
>    
>    Set newWkbk = Nothing
>    On Error Resume Next
>    Set newWkbk = Workbooks(newWkbkName)
>    On Error GoTo 0
>    If newWkbk Is Nothing Then
>        MsgBox "Please open the new " & newWkbkName & " file!"
>        Exit Sub
>    End If
>
>    For Each cBar In Application.CommandBars
>        For Each ctrl In cBar.Controls
>            If ctrl.BuiltIn Then
>                'do nothing
>            Else
>                If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
>                    Debug.Print "----" & ctrl.Caption & "----" & vbLf _
>                                      & ctrl.OnAction
>                    ExclamePos = InStr(1, ctrl.OnAction, "!")
>                    If ExclamePos > 0 Then
>                        ctrl.OnAction = newWkbk.Name _
>                                      & Mid(ctrl.OnAction, ExclamePos)
>                    End If
>                    Debug.Print ctrl.OnAction
>                End If
>            End If
>        Next ctrl
>    Next cBar
>
>End Sub
>
>==========
>But I think the easiest solution would be to create the toolbars on the fly. 
>Then you won't have to go through the next time you upgrade.
>
>
>Dennis@NoSpam.com wrote:
>> 
>> Thanks for the code Dave, it's neat!
>> 
>> Unfortunately my macro buttons are spread out over many different command bars
>> so it would be just as easy to reassign the 20 macros by leaving the
>> Tools->Customize window up and right click on each  macro icon and then assign
>> Macro to the Personal.xls!mymacro in the macro list box.
>> 
>> MS should have an easier way to do this.
>> 
>> Dennis
>> 
>> 
>> Dave Peterson <petersod@verizonXSPAM.net> wrote:
>> 
>> >First, back up files first!
>> >
>> >But this seemed to work ok for me:
>> >
>> >Option Explicit
>> >Sub testme01()
>> >
>> >    Dim cBar As CommandBar
>> >    Dim cBarName As String
>> >    Dim ctrl As CommandBarControl
>> >    Dim newWkbk As Workbook
>> >    Dim newWkbkName As String
>> >    Dim ExclamePos As Long
>> >
>> >    newWkbkName = "Personal.xls"
>> >    cBarName = "yourtoolbarnamehere"
>> >
>> >    Set newWkbk = Nothing
>> >    On Error Resume Next
>> >    Set newWkbk = Workbooks(newWkbkName)
>> >    On Error GoTo 0
>> >    If newWkbk Is Nothing Then
>> >        MsgBox "Please open the new " & newWkbkName & " file!"
>> >        Exit Sub
>> >    End If
>> >
>> >    Set cBar = Nothing
>> >    On Error GoTo 0
>> >    Set cBar = Application.CommandBars(cBarName)
>> >    On Error GoTo 0
>> >    If cBar Is Nothing Then
>> >        MsgBox "Missing commandbar named: " & cBarName
>> >        Exit Sub
>> >    End If
>> >
>> >    For Each ctrl In cBar.Controls
>> >        If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
>> >            Debug.Print "----" & ctrl.Caption & "----" & vbLf & ctrl.OnAction
>> >            ExclamePos = InStr(1, ctrl.OnAction, "!")
>> >            If ExclamePos > 0 Then
>> >                ctrl.OnAction = newWkbk.Name & Mid(ctrl.OnAction, ExclamePos)
>> >            End If
>> >            Debug.Print ctrl.OnAction
>> >        End If
>> >    Next ctrl
>> >
>> >End Sub
>> >
>> >===========
>> >But one way to avoid this problem is to build that toolbar when the workbook
>> >opens (and delete it when the workbook closes.
>> >
>> >This is how I do it:
>> >http://groups.google.co.uk/groups?threadm=40E095F1.5CB35B41%40msn.com
>> >
>> >
>> >
>> >Dennis@NoSpam.com wrote:
>> >>
>> >> In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
>> >> clicked executed the VBA code in my personal.xls file.  The excel.xlb was
>> >> located in c:\Documents and Setting\comp1\Application Data\Excel and the
>> >> personal.xls file was located in "c:\Documents and Setting\comp1\Application
>> >> Data\Excel\Xlstart".
>> >>
>> >> On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
>> >> excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
>> >> the personnal.xls file into c:\Documents and Setting\Laptop\Application
>> >> Data\Excel\Xlstart
>> >>
>> >> On the laptop when I bring Excel up the custom macro buttons are there
>> >> and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).
>> >>
>> >> However when I click on a custom macro button I get the following error:
>> >> "A document with the name personal.xls is already open.  You cannot open two
>> >> documents with the same name even if the documents are in different folders."
>> >>
>> >> I did a search on the whole disk and there is only one file named personal.xls
>> >> and it is located as above.
>> >>
>> >> If I click on Tools->Customize->right click the macro icon->assign Macro.  The
>> >> Assign Macro Window appears with the macro name c:\Documents and
>> >> Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro
>> >>
>> >> If I reassign the custom button to that same macro that shows up in the window
>> >> list below the Macro Name which shows the name Personal.XLS!mymacro, than that
>> >> custom macro button works fine after I close and reopen Excel while the other
>> >> custom buttons still won't work.
>> >>
>> >> Since I have about 20 custom buttons assigned to 20 different VBA macros it
>> >> would be cumbersome to reassign each button.
>> >>
>> >> Is there an easier way to redirect all the custom macro buttons to
>> >> Personal.XLS!?
>> >>
>> >> Thanks for any help.
>> >>
>> >> Dennis

0
Dennis8944 (79)
7/18/2005 8:48:41 PM
It may have worked, but I'd still look at building the toolbar on the fly.  It
really isn't that difficult and makes a lot simpler.

(Well, not simpler than being done--but you know what I mean <vbg>.)

Dennis@NoSpam.com wrote:
> 
> Thanks again Dave.  This code did it for all my macros.
> 
> Dennis
> 
> Dave Peterson <petersod@verizonXSPAM.net> wrote:
> 
> >Maybe just cycling through all the commandbars would help:
> >
> >Option Explicit
> >Sub testme01()
> >
> >    Dim cBar As CommandBar
> >    Dim ctrl As CommandBarControl
> >    Dim newWkbk As Workbook
> >    Dim newWkbkName As String
> >    Dim ExclamePos As Long
> >
> >    newWkbkName = "Personal.xls"
> >
> >    Set newWkbk = Nothing
> >    On Error Resume Next
> >    Set newWkbk = Workbooks(newWkbkName)
> >    On Error GoTo 0
> >    If newWkbk Is Nothing Then
> >        MsgBox "Please open the new " & newWkbkName & " file!"
> >        Exit Sub
> >    End If
> >
> >    For Each cBar In Application.CommandBars
> >        For Each ctrl In cBar.Controls
> >            If ctrl.BuiltIn Then
> >                'do nothing
> >            Else
> >                If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
> >                    Debug.Print "----" & ctrl.Caption & "----" & vbLf _
> >                                      & ctrl.OnAction
> >                    ExclamePos = InStr(1, ctrl.OnAction, "!")
> >                    If ExclamePos > 0 Then
> >                        ctrl.OnAction = newWkbk.Name _
> >                                      & Mid(ctrl.OnAction, ExclamePos)
> >                    End If
> >                    Debug.Print ctrl.OnAction
> >                End If
> >            End If
> >        Next ctrl
> >    Next cBar
> >
> >End Sub
> >
> >==========
> >But I think the easiest solution would be to create the toolbars on the fly.
> >Then you won't have to go through the next time you upgrade.
> >
> >
> >Dennis@NoSpam.com wrote:
> >>
> >> Thanks for the code Dave, it's neat!
> >>
> >> Unfortunately my macro buttons are spread out over many different command bars
> >> so it would be just as easy to reassign the 20 macros by leaving the
> >> Tools->Customize window up and right click on each  macro icon and then assign
> >> Macro to the Personal.xls!mymacro in the macro list box.
> >>
> >> MS should have an easier way to do this.
> >>
> >> Dennis
> >>
> >>
> >> Dave Peterson <petersod@verizonXSPAM.net> wrote:
> >>
> >> >First, back up files first!
> >> >
> >> >But this seemed to work ok for me:
> >> >
> >> >Option Explicit
> >> >Sub testme01()
> >> >
> >> >    Dim cBar As CommandBar
> >> >    Dim cBarName As String
> >> >    Dim ctrl As CommandBarControl
> >> >    Dim newWkbk As Workbook
> >> >    Dim newWkbkName As String
> >> >    Dim ExclamePos As Long
> >> >
> >> >    newWkbkName = "Personal.xls"
> >> >    cBarName = "yourtoolbarnamehere"
> >> >
> >> >    Set newWkbk = Nothing
> >> >    On Error Resume Next
> >> >    Set newWkbk = Workbooks(newWkbkName)
> >> >    On Error GoTo 0
> >> >    If newWkbk Is Nothing Then
> >> >        MsgBox "Please open the new " & newWkbkName & " file!"
> >> >        Exit Sub
> >> >    End If
> >> >
> >> >    Set cBar = Nothing
> >> >    On Error GoTo 0
> >> >    Set cBar = Application.CommandBars(cBarName)
> >> >    On Error GoTo 0
> >> >    If cBar Is Nothing Then
> >> >        MsgBox "Missing commandbar named: " & cBarName
> >> >        Exit Sub
> >> >    End If
> >> >
> >> >    For Each ctrl In cBar.Controls
> >> >        If InStr(1, ctrl.OnAction, newWkbkName, vbTextCompare) > 0 Then
> >> >            Debug.Print "----" & ctrl.Caption & "----" & vbLf & ctrl.OnAction
> >> >            ExclamePos = InStr(1, ctrl.OnAction, "!")
> >> >            If ExclamePos > 0 Then
> >> >                ctrl.OnAction = newWkbk.Name & Mid(ctrl.OnAction, ExclamePos)
> >> >            End If
> >> >            Debug.Print ctrl.OnAction
> >> >        End If
> >> >    Next ctrl
> >> >
> >> >End Sub
> >> >
> >> >===========
> >> >But one way to avoid this problem is to build that toolbar when the workbook
> >> >opens (and delete it when the workbook closes.
> >> >
> >> >This is how I do it:
> >> >http://groups.google.co.uk/groups?threadm=40E095F1.5CB35B41%40msn.com
> >> >
> >> >
> >> >
> >> >Dennis@NoSpam.com wrote:
> >> >>
> >> >> In Excel 2000 on my WinXP Pro computer I had custom macro buttons that when
> >> >> clicked executed the VBA code in my personal.xls file.  The excel.xlb was
> >> >> located in c:\Documents and Setting\comp1\Application Data\Excel and the
> >> >> personal.xls file was located in "c:\Documents and Setting\comp1\Application
> >> >> Data\Excel\Xlstart".
> >> >>
> >> >> On my new laptop with Win XP Home I reinstalled Excel 2000 and put the
> >> >> excel.xlb file in c:\Documents and Setting\Laptop\Application Data\Excel and put
> >> >> the personnal.xls file into c:\Documents and Setting\Laptop\Application
> >> >> Data\Excel\Xlstart
> >> >>
> >> >> On the laptop when I bring Excel up the custom macro buttons are there
> >> >> and the personal.xls file is loaded ( I can see it in the Visual Basic Editor).
> >> >>
> >> >> However when I click on a custom macro button I get the following error:
> >> >> "A document with the name personal.xls is already open.  You cannot open two
> >> >> documents with the same name even if the documents are in different folders."
> >> >>
> >> >> I did a search on the whole disk and there is only one file named personal.xls
> >> >> and it is located as above.
> >> >>
> >> >> If I click on Tools->Customize->right click the macro icon->assign Macro.  The
> >> >> Assign Macro Window appears with the macro name c:\Documents and
> >> >> Setting\comp1\Application Data\Excel\Xlstart\Personal.xls!mymacro
> >> >>
> >> >> If I reassign the custom button to that same macro that shows up in the window
> >> >> list below the Macro Name which shows the name Personal.XLS!mymacro, than that
> >> >> custom macro button works fine after I close and reopen Excel while the other
> >> >> custom buttons still won't work.
> >> >>
> >> >> Since I have about 20 custom buttons assigned to 20 different VBA macros it
> >> >> would be cumbersome to reassign each button.
> >> >>
> >> >> Is there an easier way to redirect all the custom macro buttons to
> >> >> Personal.XLS!?
> >> >>
> >> >> Thanks for any help.
> >> >>
> >> >> Dennis

-- 

Dave Peterson
0
petersod (12005)
7/18/2005 11:56:17 PM
Reply:

Similar Artilces:

Killer macro idea for business mailings
One of you programmers should write a macro to validate a city from zip code. I receive excel files from response cards and a lot of people will put some random letters for their city instead of the actual city name, or maybe they'll put the wrong abrieviation for their state. It would be awesome if all the zipcodes could validate the state field and change if necessary and the same for the city fields too. Okay, have at it :) Okay, well good idea I think anyways, but it turns out that my columns just weren't wide enough so it didn't display more then 2 letters. ...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Can I copy radio buttons so that the second set is independent of.
I have a group of four radio buttons that all point to one cell. My goal is to create a second set that looks identical to the first but which points to a second cell. This is for a questionaire that will have many questions with the same four answer values. I want to be able to quickly create 100 button groups that will each update a separate cell for scoring of the questionaire. There are optionbuttons on the Control toolbox toolbar and there are optionbuttons on the Forms toolbar. Each has different behaviors. If I had to use lots, I'd use the Forms version. Here's a pos...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

"Customize Outlook Today" does not respond
The "Customize Outlook Today" button no longer functions. I use Outlook 2000 on two separate computers. the Customize feature works on neither one. They both formerly functioned perfectly. I have noted a few other postings with the same issue, but there have been no responses. Is it possible that one of the updates to Outlook 2000 has rendered this feature inoperable? Since I keep my programs up to date, that is the only guess I have. I have the exact same problem on more than one pc. I have been searching for an answer all day. I just decided to start using the opti...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Moving Exchange #5
I am needing to move my Exchange server off of the SBS box that it is currently on and move it to another, new server. I cannot keep the servers the same name as I need the old server to continue to run SQL. Are there white papers on how to do this? TIA Scott T. On Fri, 18 Aug 2006 08:43:40 -0700, scottdog129 <scottdog129@discussions.microsoft.com> wrote: >I am needing to move my Exchange server off of the SBS box that it is >currently on and move it to another, new server. I cannot keep the servers >the same name as I need the old server to continue to run SQL. Are...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Outlook 2003
In Outlook 2003, #1 Is there a way to refresh the unread folder so that read messages no longer apear? Right now I have to click closed the unread folder and click it again #2 Is there a way to create a toolbar button that goes directly to a subfolder? Thanks ...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Custom reports #3
Hi, I want to pull a report on how many customers I am getting every hour of the day. Does anyone out there know how to do that? I can try writing a query if someone can tell me which table I can find this information in (timestamp, transaction etc). Any help is appreciated. Regards JD This info is given at the end of the Z report. If you want to use it in a query open the Z report recipt file using the Notepad and see the fields that are used... Report.Hour.HourSales etc... Hope this helps... Carlos "JD" wrote: > Hi, > > I want to pull a report on how many cust...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Fulfillment type orders
We have orders at the initial status (just entered) and the options to allow delete documents and void documents are enabled on the Sales Order Type. However, we can't get a void or delete button unless we move the fulfillment order through the confirm process to an "Invoice" to get a Delete? What's up with this? Very annoying. Jana, I know it is subtle and wish there was a better way to display it, but the Void option only applies to Invoice type IDs and not Fulfillment Order type IDs ("Void Invoice Documents" vs. "Delete Documents"). Since both t...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...