Question about excel instances locked down if spreadsheet hidden while using a form

[CharlieG]

Hi All,

we have just identified a problem when using a form coded in excel VBA - when the form is displayed (which has been coded to hide / make invisible the workbook it relates to by making the window invisible) nothing else can be done in any other of excel that is running- cannot even minimise or move one of the other open excel windows.

Has anyone seen a workaround for this so that the form can be open with just the related workbook hidden and other open excel workbooks being usable at the same time?

Is it a code adjustment or would the form have to be coded in a different language such as VB rather than VBA?

We considered recoding it and putting it on an SQL server but the company felt that it was not worth it due to the relatively small, limited amount of data compared to our existing SQL databases which hold tens of thousands of records.

Many thanks for any suggestions as internet searches have not yet helped us further either and we do not want wave a white flag of surrender on VBA for this form just yet!

[CharlieG]

update - solution found!

just had this pinged over to my colleague - seems to work nicely.

Put this code in Microsoft Excel Objects à ThisWorkbook




Private Sub Workbook_Open()

If Application.Workbooks.Count > 1 Then
ThisWorkbook.Windows(1).Visible = False
Else
Application.Visible = False
End If

UserForm1.Show vbModeless
End Sub

Only change made was to add the vbModeless to the coding!

Hope this proves helpful to others.

[SamT]

If Application.Workbooks.Count > 1 Then
ThisWorkbook.Windows(1).Visible = False
Else
Application.Visible = False
End If

In a single line

ThisWorkbook.Windows(1).Visible = Workbooks.Count = 1