2003 to 2007

[chandrashekar]

Hi,

I have a code in Office 2003 which is working fine, but if I run same in Office 2007 code is not working.

Is there any way to convert code to 2007.

Thanks in advance.

Thanks,

Chandra Shekar B

 

[RoryA]

That should be:

.SetSourceData chrtrng, xlRows

 

[chandrashekar]

Thanks a lot its working. May I know is there any way to convert all VBA 2003 code to 2007. Some code will not work in 2007 for ex:

Dim mycb As CommandBar
Dim mycbtn1 As CommandBarButton
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Create Report").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Create Report").Delete
Set mycb = Application.CommandBars.Add("Create Report", msoBarFloating)
Set mycbtn1 = mycb.Controls.Add(msoControlButton)
On Error GoTo 0
With mycbtn1
.Caption = "&Create Report"
.Style = msoButtonCaption
.Height = 30
.Width = 100
.OnAction = "run_report"
.Visible = True
End With
With mycb
.Top = Abs(Application.UsableHeight - Application.Height) + 1
.Visible = True
End With
End Sub

 

[RoryA]

In what way does that not work?
There is no simple answer. Most code will work without issue but there are some changes such as the new Ribbon interface that mean the results may not be what you expect.

 

[chandrashekar]

In 2003 I am getting Tool bar by name Create Report but in 2007 I am not getting Tool bar itself.

 

[RoryA]

The buttons will be added to the Addins tab in the Ribbon if you create commandbars in code in 2007 or later, due to the new menu interface.

 

[chandrashekar]

Hi,

In 2003 I am getting tool bar by name Create Report but in 2007 I am not getting tool bar itself, is any code there which can work for all the office versions.

 

[RoryA]

No, because you cannot create new toolbars in 2007 or later. The buttons will always be added to the Addins tab. In 2007 or later you can create new tabs on the ribbon by altering the XML of the workbook, assuming it is a 2007 format workbook.

 

[chandrashekar]

How to come over this problem should I write code separately for both the version. Please let me know how to do this? Thanks!

 

[RoryA]

This shows you how to change the Ribbon in 2007 or later.

 

[chandrashekar]

Thanks a lot.