Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
APPLICATION OR USER DEFINED ERROR
Hello,
I have a simple excel workbook called Report Menu. When it opens it has a user form with labels which each hyper link to a different report. Eg; Application.ThisWorkbook.FollowHyperlink "S:\Lists\Analysis\CVC METRICS\Customer Metrics.xls" Windows("Report Menu.xls").Activate ActiveWorkbook.Close False On each of the linked forms there is a command button currently to return to the menu, eg; Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls" When the report menu opens, it runs on Open this; Sub CheckIfOpen() UserForm1.Show On Error Resume Next Workbooks("Customer Metrics.xls").Close SaveChanges:=False On Error GoTo 0 End Sub I have tested this and it works ok, ie. goes back and forth no problem except that, I seem to get an egg timer which stays on for quite some time in teh customer metrics. Is this becuase of this line; Selection.QueryTable.Refresh BackgroundQuery:=False ?? My question is this, I found some code to have a user defined menu at the top so I can for instance navigate to different sheets etc. When I use the menu to do the same as the command button eg; Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls" It does return to the menu but, I get an APPLICATION OR USER-DEFINED ERROR? I don't know if this is this is part of problem but the first time I open the Customer Metrics, this code works; Private Sub Workbook_Open() Run ("AddMenus") '-- in use to avoid use of volatile Application.CalculateFull ' ctrl-alt-f9 Sheets("SAS Source").Visible = True Sheets("SAS Source").Activate Cells(5, 5).Select Selection.QueryTable.Refresh BackgroundQuery:=False On Error GoTo 0 Sheets("SAS Source").Visible = False End Sub ....but when I return to the Report Menu and then reopen the customer metrics, the code does not work, I.e. A new menu is not added |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
APPLICATION OR USER DEFINED ERROR
Hi,
Could you provide more information? For example, to what code are you referring here? My question is this, I found some code to have a user defined menu at the top so I can for instance navigate to different sheets etc. When I use the It seems there may be some conflict. I don't see this how you are using this instruction: Selection.QueryTable.Refresh BackgroundQuery:=False ?? Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
APPLICATION OR USER DEFINED ERROR
Hi Dan,
Thanks for taking the time to reply. The code I found was this; Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCutomMenu As CommandBarControl '(1)Delete any existing one. We must use On Error Resume next _ in case it does not exist. On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("&Customer Metrics Menu").delete On Error GoTo 0 '(2)Set a CommandBar variable to Worksheet menu bar Set cbMainMenuBar = _ Application.CommandBars("Worksheet Menu Bar") '(3)Return the Index number of the Help menu. We can then use _ this to place a custom menu before. iHelpMenu = _ cbMainMenuBar.Controls("Help").Index '(4)Add a Control to the "Worksheet Menu Bar" before Help. 'Set a CommandBarControl variable to it Set cbcCutomMenu = _ cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _ Befo=iHelpMenu) '(5)Give the control a caption cbcCutomMenu.Caption = "&Customer Metrics Menu" '(6)Working with our new Control, add a sub control and _ give it a Caption and tell it which macro to run (OnAction). With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Update Metrics" .FaceId = 50 .OnAction = "UpdateRoutine" End With What I did have and would like to have is; With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Exit Metrics" .FaceId = 51 .OnAction = "ExitRoutine" Where the macro, ExitRoutine. would simply be; Sub ExitRoutine () On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("&Customer Metrics Menu").delete On Error GoTo 0 Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls" End Sub In regards to; Selection.QueryTable.Refresh BackgroundQuery:=False ?? Each worksheet is linked to Access Data e.g. Data/Get External Data....etc Before I put in this line, when the Customer Metrics was opened from, The Report Menu, it would not refresh and adding this line was the only way I knew of acheiving it. "dan dungan" wrote: Hi, Could you provide more information? For example, to what code are you referring here? My question is this, I found some code to have a user defined menu at the top so I can for instance navigate to different sheets etc. When I use the It seems there may be some conflict. I don't see this how you are using this instruction: Selection.QueryTable.Refresh BackgroundQuery:=False ?? Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
APPLICATION OR USER DEFINED ERROR
Hi Sean,
My question is this, I found some code to have a user defined menu at the top so I can for instance navigate to different sheets etc. When I use the menu to do the same as the command button eg; Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls" It does return to the menu but, I get an APPLICATION OR USER-DEFINED ERROR? When you get the application or user defined error, what line of code is highlighted when you click on debug? Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; | Excel Programming | |||
Runtime Error 1004: Application defined or object defined error | Excel Programming | |||
Run Time Error 1004 - Application-defined or object-defined error | Excel Programming | |||
error: ActiveCell.Offset(0, -1).Select = Application-defined or object-defined error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |