How to Create a Modeless Form VBA

[wcm69]

I have the below code in my workbook module and I'd like to make it modeless so I can work in the active spreadsheet while it is also active. I want it to also stay visible and active through out (i.e. as long as I'm in a particular spreadsheet) because I've put Command Buttons on the form to navigate between worksheets - of which there are 51.

I should mention the code I'm using positions the Form and hides the [x] Close button and Title bar.

Any help would be greatly appreciated.

Thanks in advance

Code being used:

Private Sub UserForm_Activate()

Me.StartUpPosition = 0
Me.Top = Application.Top + 170
Me.Left = Application.Left + Application.Width - Me.Width - 40

End Sub

Private Sub UserForm_Initialize()
HideTitleBar Me

End Sub

 

[MARK858]

You can only delete your own posts within the 10 minutes if you have been granted MVP status.

 

[Rick Rothstein]

You can only delete your own posts within the 10 minutes if you have been granted MVP status.

Hmm, I guess that could be although I always thought it was part of the editing process. At least I now know not to suggest that any more.

 

[MARK858]

Hmm, I guess that could be although I always thought it was part of the editing process.

It is one of the options under Edit Usergroup - Post / Thread Permissions in vBulletin.

It is normal practice to restrict it's usage as irresponsible deletions of posts can leave other posts in the thread making no sense (although you can achieve the same effect with an edit)

 

[wcm69]

Thank you All Responders.

I've used Logit's suggestion re. Changing the Properties box 'ShowModal' to FALSE, which seemed the easiest solution - being new to VBA. It works perfectly, and now allows me to scroll and access cells in the worksheet whilst the Form is activated.

I'm now hoping one of you may be kind enough to give me a little hand (i.e. codes - with a little instruction) to get the form to only show (automatically) in the specific worksheets that I need to have the Form (it has Command buttons) navigate between the worksheets.

I'm going to need the form to show / stay active in 51 of my 53 worksheets. If you can help with this last issue, I'd be very grateful. Alternatively I will post it in a new thread if you so suggest.

As usual your time and patience is always appreciated.

Thanks in advance

I got the codes to remove the [X] close and title bar from the form on the internet. See the below link.


Remove Window Border Title Bar around UserForm with VBA - wellsr.com

 

[My Aswer Is This]

The form will stay open till you close the form. You can navigate to other sheets and the form will stay open.
Are you now saying you are going to close the form every time you move to another sheet and you only want it visible on certain sheets? The whole purpose of making it modeless is so it can stay open while you work on your sheets.

 

[AkaTrouble]

you could add a sheet code to each sheet that sets the user form visible or hidden on activate the sheet

this code would have to be added to all sheets but would achieve your wish ( i think )

use the worksheet activate option with code in sheet module

Private Sub Worksheet_Activate()

End Sub

edit sample code to hide userform

If UserForm1.Visible Then
    UserForm1.Hide
    Else
    UserForm1.Show False
End If

 

[Logit]

Or ...

You could utilize the ThisWorkbook module to cover all sheets with one macro :

Option Explicit


Private Sub Workbook_Open()
    'UserForm1.Show
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then   '<-- Sheet 2 could be 52, Sheet 3 could be 53
        UserForm1.Hide
    Else
        UserForm1.Show
    End If
End Sub

 

[AkaTrouble]

Or ... You could utilize the ThisWorkbook module to cover all sheets with one macro :

good solution and also makes it dynamic on sheets added and removed

code stolen and saved lol

 

[Logit]

"stolen" ????

omg !



Thanks for the nice words ....


Oh ... forget about this part of the macro :

Private Sub Workbook_Open()
    'UserForm1.Show
End Sub

It can be deleted from the macro. It doesn't do anything.

 

[wcm69]

Again...A massive thanks to all those who've taken their time to respond.

I've tried all of the above and combinations of each but nothings currently doing what I need. However I think a mix of the above should achieve what I'm after. Perhaps if I try to explain more clearly how my workbook has been set up. Simply - my workbook contains 51 worksheets (plus a contents page) which lists Products and their Suppliers.

Each of these Products has been categorised into Product Ranges - of which their are 17.
I have put each of the 17 Product Ranges on 3 tabs, hence 51 worksheets.

As a 'labour of love!' I've built 17 Userforms and named them (in accordance with the tab names).

So what I want is for 1 Userform (of 17) to be active / visible in 3 active worksheets only. All the other 16 Userforms are to stay invisible / non-active until their particular worksheet is then open / activated.

I think AkaTrouble's suggestion though a longer way to do it may be the solution in my case where each Userform is specific to it's (3) worksheets.

Again, I'm hoping the above is clear and someone can provide a code/s (with a few instruction) that will do the trick.

Thanks in advance