I have created a form which creates a combo box from another open file. If
that other file isn't open I want to be able to trap the error with a msgbox.
However I can't find a way of the form not being displayed (which results in
nothing in the combo box).

I hope this makes sense

Any ideas?

Many thanks

James

Hi James,

You can actually test if the workbook is open with the following code. I
have included the code to open the workbook if not already open so you can
please yourself if you want to use this method or just use the msgbox and
then Exit Sub.

Sub OpenWorkbook()
Dim strComboDataPath
Dim strComboDataWb As String
Dim wbComboData As Workbook

'Can edit ThisWorkbook.Path in the _
following line and use actual path _
enclosed in double quotes
strComboDataPath = ThisWorkbook.Path & "\"

strComboDataWb = "Combo Population Data.xlsm"

On Error Resume Next

Set wbComboData = Workbooks(strComboDataWb)

If Err.Number 0 Then 'Workbook not open
'MsgBox for testing only
MsgBox "Workbook " & strComboDataWb & " not open"

On Error GoTo 0 'Resume error trapping ASAP

'If required open the workbook as follows
Workbooks.Open Filename:= _
strComboDataPath & strComboDataWb

Set wbComboData = Workbooks(strComboDataWb)

End If

On Error GoTo 0 'Cancel resume next

'Your code to initialize userform and populate the combobox here

MsgBox wbComboData.Name 'for testing only

End Sub


--
Regards,

OssieMac

Thanks Ossie. Unfortunately I didn't explain myself clearly enough. I was
using the update combobox as an example. However what I really want to know
is how to stop a form from being displayed if there is a problem in the form
intialisaton.

Hope this makes sense

James

"OssieMac" wrote:

Hi James,

You can actually test if the workbook is open with the following code. I
have included the code to open the workbook if not already open so you can
please yourself if you want to use this method or just use the msgbox and
then Exit Sub.

Sub OpenWorkbook()
Dim strComboDataPath
Dim strComboDataWb As String
Dim wbComboData As Workbook

'Can edit ThisWorkbook.Path in the _
following line and use actual path _
enclosed in double quotes
strComboDataPath = ThisWorkbook.Path & "\"

strComboDataWb = "Combo Population Data.xlsm"

On Error Resume Next

Set wbComboData = Workbooks(strComboDataWb)

If Err.Number 0 Then 'Workbook not open
'MsgBox for testing only
MsgBox "Workbook " & strComboDataWb & " not open"

On Error GoTo 0 'Resume error trapping ASAP

'If required open the workbook as follows
Workbooks.Open Filename:= _
strComboDataPath & strComboDataWb

Set wbComboData = Workbooks(strComboDataWb)

End If

On Error GoTo 0 'Cancel resume next

'Your code to initialize userform and populate the combobox here

MsgBox wbComboData.Name 'for testing only

End Sub


--
Regards,

OssieMac

Hi James,

If it produces an error then you can trap that otherwise need to know
exactly what is supposed to be on the form and go through the exercise of
testing each control and to do that one needs all of the info regarding the
controls.

--
Regards,

OssieMac


"James Price at Premier" wrote:

Thanks Ossie. Unfortunately I didn't explain myself clearly enough. I was
using the update combobox as an example. However what I really want to know
is how to stop a form from being displayed if there is a problem in the form
intialisaton.

Hope this makes sense

James

"OssieMac" wrote:

Hi James,

You can actually test if the workbook is open with the following code. I
have included the code to open the workbook if not already open so you can
please yourself if you want to use this method or just use the msgbox and
then Exit Sub.

Sub OpenWorkbook()
Dim strComboDataPath
Dim strComboDataWb As String
Dim wbComboData As Workbook

'Can edit ThisWorkbook.Path in the _
following line and use actual path _
enclosed in double quotes
strComboDataPath = ThisWorkbook.Path & "\"

strComboDataWb = "Combo Population Data.xlsm"

On Error Resume Next

Set wbComboData = Workbooks(strComboDataWb)

If Err.Number 0 Then 'Workbook not open
'MsgBox for testing only
MsgBox "Workbook " & strComboDataWb & " not open"

On Error GoTo 0 'Resume error trapping ASAP

'If required open the workbook as follows
Workbooks.Open Filename:= _
strComboDataPath & strComboDataWb

Set wbComboData = Workbooks(strComboDataWb)

End If

On Error GoTo 0 'Cancel resume next

'Your code to initialize userform and populate the combobox here

MsgBox wbComboData.Name 'for testing only

End Sub


--
Regards,

OssieMac

OssieMac

Does that mean that I can't unload a form during initialisation?

Many thanks

James

"OssieMac" wrote:

Hi James,

If it produces an error then you can trap that otherwise need to know
exactly what is supposed to be on the form and go through the exercise of
testing each control and to do that one needs all of the info regarding the
controls.

--
Regards,

OssieMac


"James Price at Premier" wrote:

Thanks Ossie. Unfortunately I didn't explain myself clearly enough. I was
using the update combobox as an example. However what I really want to know
is how to stop a form from being displayed if there is a problem in the form
intialisaton.

Hope this makes sense

James

"OssieMac" wrote:

Hi James,

You can actually test if the workbook is open with the following code. I
have included the code to open the workbook if not already open so you can
please yourself if you want to use this method or just use the msgbox and
then Exit Sub.

Sub OpenWorkbook()
Dim strComboDataPath
Dim strComboDataWb As String
Dim wbComboData As Workbook

'Can edit ThisWorkbook.Path in the _
following line and use actual path _
enclosed in double quotes
strComboDataPath = ThisWorkbook.Path & "\"

strComboDataWb = "Combo Population Data.xlsm"

On Error Resume Next

Set wbComboData = Workbooks(strComboDataWb)

If Err.Number 0 Then 'Workbook not open
'MsgBox for testing only
MsgBox "Workbook " & strComboDataWb & " not open"

On Error GoTo 0 'Resume error trapping ASAP

'If required open the workbook as follows
Workbooks.Open Filename:= _
strComboDataPath & strComboDataWb

Set wbComboData = Workbooks(strComboDataWb)

End If

On Error GoTo 0 'Cancel resume next

'Your code to initialize userform and populate the combobox here

MsgBox wbComboData.Name 'for testing only

End Sub


--
Regards,

OssieMac

James

If there is an error in the form's initialization then it won't show
it'll just, well, error.

I think you might have problems unloading a form during
initialization, why would you want to do that anyway?

Why not check whatever you need to check, eg if the other workbook is
open, before you open the form?

By the way I tried unloading a form in it's initialize event and all
sorts of error trapping - nothing worked.

Thanks Norie

You're riight I was trying to be lazy and do the checking on
initialisation. The error messages are quite odd though

Cheers



"norie" wrote:

James

If there is an error in the form's initialization then it won't show
it'll just, well, error.

I think you might have problems unloading a form during
initialization, why would you want to do that anyway?

Why not check whatever you need to check, eg if the other workbook is
open, before you open the form?

By the way I tried unloading a form in it's initialize event and all
sorts of error trapping - nothing worked.

.