convert VB code from 2000 to XP 1

[Dan15]

This piece of VB code is crucial to my application - unfortunately the OS was changed to XP and now it comes up as an error. Can someone please tell me what needs to be changed to make this work?
Thank you-

-Dan


Sub FixControl(objControl As CommandBarControl)
Dim oC As CommandBarControl, p As Integer
If TypeName(objControl) = "CommandBarPopup" Then
For Each oC In objControl.Controls
FixControl oC 'recursive call
Next oC
Else
p = InStr(objControl.OnAction, "!&quot
If p > 0 Then objControl.OnAction = Mid(objControl.OnAction, p + 1)
End If
End Sub

Sub FixCustomToolbar(strToolBar As String)
Dim objControl As CommandBarControl
For Each objControl In Application.CommandBars(strToolBar).Controls
FixControl objControl
Next objControl
End Sub

 

[ETID]

Maybe...

Just adding the proper Ref. Library will work

....from the menu in the VBA editor

 

[RobBroekhuis]

Even though I have no access to XP, I can suggest a start to troubleshooting this:

In the immediate window in VBE, type

?typename(application.commandbars(strtoolbar))
and
?typename(application.commandbars(strtoolbar).controls(1))

and report back the results. Also, you might want to try to

dim oC As Object

instead of a CommandBarControl, and see how that goes. Rob

 

[Dan15]

Rob

It didn't like this line

?typename(application.commandbars(strtoolbar))

the reply was

Compile error:
invalid outside procedure

dim oC as object did not change things either

It is probably a good thing you do not have XP

ETID - can you explain which Ref Library I need to add please.

Thanks for the help

 

[RobBroekhuis]

Did you do this after the code gave the error and you hit the "debug" button? If not, the variable strtoolbar would not be defined, which would cause the error in the immediate window. Let the error occur, hit debug, check the value of strtoolbar, and then try to find out the typenames of the two objects/properties as above. I think the proper way to resolve this is to work in the native object model of XL XP, in which case you really would not need to set other references. It shouldn't be difficult - just requires understanding in which subtle way the object model has changed in XP.
Rob

 

[ETID]

If you can find a machine with the previous O.S.

Load your db on it and look int the vba editor and select tools>references....

then write down all the libraries that are checked.


then on the xp machine ... load those libraries.

 

[RobBroekhuis]

ETID,
I think Dan's code example above would run on 2000 without any libraries referenced besides the basic VBA and Excel libraries. It's all basic Excel object model stuff.
Rob

 

[Dan15]

Rob,

Sorry, but I'm not real sure on what the procedure is here -

I open the workbook, activate macros, and the folllowing error message comes up:

Run-Time error '5':
Invalid procedure call or argument

I hit debug and it takes me to this line:

For Each objControl In Application.CommandBars(strToolBar).Controls

which is hi-lighted yellow.

Now, I'm not sure where I insert
?typename(application.commandbars(strtoolbar))
and
?typename(application.commandbars(strtoolbar).controls(1))

Please advise-

-Dan

 

[RobBroekhuis]

The "immediate window" is the window at the bottom right of the Visual Basic editor (if it's not there, hit Ctrl-G to get to it). You can use this window to program, change and query properties and variables on the fly. After you hit debug, just paste those queries into the window, hit enter and record the response.
Rob

 

[Dan15]

OK, I put the queries into the immediate window and hit return, but the same
Run-Time error '5':
Invalid procedure call or argument

comes up, refering to the same hi-lighted line.

 

[RobBroekhuis]

Let's start with even more basic matters, then. In the immediate window, after going into debug mode, type
?strToolBar
and record the result. Also, type

?typename(application.commandbars)

and let us know what Excel reports back.
Rob

 

[Dan15]

Here are the results:

?strToolBar
Functions
?typename(application.commandbars)
CommandBars

 

[RobBroekhuis]

Okay, so far so good. Next step is to make sure the toolbar named "Functions" exists. From the error you were getting previously, I suspect it doesn't (i.e., its name may have changed from the XL2000 implementation). Check:

in Excel, use View-Toolbars, and make sure that your custom toolbar appears in the dropdown menu with name "Functions". Also check the following in the immediate window:

?application.commandbars("Functions&quot.index

Rob

 

[Dan15]

You are right, it does not have the toolbar "Functions"

The name is "Bidding004" - but does your code rever to either name?

I entered
?application.commandbars("Bidding004&quot.index

Response was:

25

 

[RobBroekhuis]

The main macro you're using has the heading:

Sub FixCustomToolbar(strToolBar As String)

The fact the strToolBar has the value "Functions" means that when you actually call this macro, you call it with the statement:

FixCustomToolbar("Functions&quot

you should use

FixCustomToolbar("Bidding004&quot

instead. Do you know where do you put the code that calls the fix macro? It's probably in Workbook_Open, on the ThisWorkbook code sheet.

Rob

 

[Dan15]

Actually, both of the following lines are there:

Private Sub Workbook_Open()
FixCustomToolbar "Bidding004"
FixCustomToolbar "Functions"
End Sub

There were 2 custom menus before - one being "functions" the other being "bidding004". Now there is only bidding004

 

[RobBroekhuis]

Okay - then that's the problem. Just remove the one for Functions, and the error will go away.
Good luck with the rest of your conversion to XP!
Rob

 

[Dan15]

You're right, seems to work fine now-

Once again, thank you very much!

-Dan