Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
I'm trying to hide the workbook that contains my macro library. This used to
work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
Instead of wb.visible = false, you want to hide the window:
Windows(ThisWorkbook.Name).Visible = False Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Allen_N" wrote in message ... I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
I can't find anything that says the visible property applies to workbooks.
"Allen_N" wrote: I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
Thanks, Bob.
Now I'm trying: Windows(wb.Name).Visible = False but I still get "Object doesn't support this property or method". I confirmed that wb.Name = "PERSONAL.XLS". "Bob Flanagan" wrote: Instead of wb.visible = false, you want to hide the window: Windows(ThisWorkbook.Name).Visible = False Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Allen_N" wrote in message ... I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
You're right. Serves me right for trapping an error and not testing it.
"JLGWhiz" wrote: I can't find anything that says the visible property applies to workbooks. "Allen_N" wrote: I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
This may get you over the hump:
Dim wkbk As Workbook Dim myWindow As Window Set wkbk = ActiveWorkbook For Each myWindow In wkbk.Windows myWindow.Visible = False Next myWindow Allen_N wrote: Thanks, Bob. Now I'm trying: Windows(wb.Name).Visible = False but I still get "Object doesn't support this property or method". I confirmed that wb.Name = "PERSONAL.XLS". "Bob Flanagan" wrote: Instead of wb.visible = false, you want to hide the window: Windows(ThisWorkbook.Name).Visible = False Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Allen_N" wrote in message ... I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
Nope. debugging reveals that ActiveWorkbook is Nothing.
"Dave Peterson" wrote: This may get you over the hump: Dim wkbk As Workbook Dim myWindow As Window Set wkbk = ActiveWorkbook For Each myWindow In wkbk.Windows myWindow.Visible = False Next myWindow Allen_N wrote: Thanks, Bob. Now I'm trying: Windows(wb.Name).Visible = False but I still get "Object doesn't support this property or method". I confirmed that wb.Name = "PERSONAL.XLS". "Bob Flanagan" wrote: Instead of wb.visible = false, you want to hide the window: Windows(ThisWorkbook.Name).Visible = False Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Allen_N" wrote in message ... I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
Dave,
Ignore my last message. After I first tried your code, I closed excel, reopened a workbook, and the macro libraries (stored in XLS's in \XLSTART) were hidden as desired. Thanks! "Allen_N" wrote: Nope. debugging reveals that ActiveWorkbook is Nothing. "Dave Peterson" wrote: This may get you over the hump: Dim wkbk As Workbook Dim myWindow As Window Set wkbk = ActiveWorkbook For Each myWindow In wkbk.Windows myWindow.Visible = False Next myWindow Allen_N wrote: Thanks, Bob. Now I'm trying: Windows(wb.Name).Visible = False but I still get "Object doesn't support this property or method". I confirmed that wb.Name = "PERSONAL.XLS". "Bob Flanagan" wrote: Instead of wb.visible = false, you want to hide the window: Windows(ThisWorkbook.Name).Visible = False Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Allen_N" wrote in message ... I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't hide workbook or trap error
You'd still want to cycle through all the workbooks:
Option Explicit Sub auto_open() Dim i As Long Dim wb As Workbook Dim myWindow As Window For i = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(1, wb.Name, "personal.xls", vbTextCompare) 0 _ Or InStr(1, wb.Name, "custom.xls", vbTextCompare) 0 Then For Each myWindow In wb.Windows myWindow.Visible = False Next myWindow End If Next i End Sub Allen_N wrote: Nope. debugging reveals that ActiveWorkbook is Nothing. "Dave Peterson" wrote: This may get you over the hump: Dim wkbk As Workbook Dim myWindow As Window Set wkbk = ActiveWorkbook For Each myWindow In wkbk.Windows myWindow.Visible = False Next myWindow Allen_N wrote: Thanks, Bob. Now I'm trying: Windows(wb.Name).Visible = False but I still get "Object doesn't support this property or method". I confirmed that wb.Name = "PERSONAL.XLS". "Bob Flanagan" wrote: Instead of wb.visible = false, you want to hide the window: Windows(ThisWorkbook.Name).Visible = False Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Allen_N" wrote in message ... I'm trying to hide the workbook that contains my macro library. This used to work, but has suddenly failed in 2 ways: the line "wb.Visible = False" produces the error "Object doesn't support this property or method" and the error is not trapped. Sub auto_open() Dim i&, wb For i& = 1 To Workbooks.Count Set wb = Workbooks(i&) If InStr(UCase$(wb.Name), "PERSONAL.XLS") Then GoSub HideWB If InStr(UCase$(wb.Name), "CUSTOM.XLS") Then GoSub HideWB Next i& Exit Sub HideWB: On Error GoTo SkipHide wb.Visible = False SkipHide: On Error GoTo 0 Return End Sub What could be going wrong? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error Trap | Excel Programming | |||
Error Trap | Excel Programming | |||
error trap | Excel Programming |