Keep excel built in user form on top of sheet when opened

[interested]

This should be easy, but i cant seem to find the answer !! The built in excel user form function is fine for what i want to do, as i just need to see the whole fields available, without VBA etc. question is. How can i keep the form on top when file openeduser form.jpg i have the user form button on my quick access toolbar, but there are lots of people who enter data into it, and i cant account for everone. Any help is much appreciated............

[6StringJazzer]

Can you clarify the problem? The automatic form is modal, so it is always on top and you can't activate any part of the worksheet until you close the form.

Do you mean that you want the form to open automatically when the workbook is opened?

[interested]

Yes. I would like the built in form visable when you open a work book by default.

[6StringJazzer]

You need a macro to do this.

Are you familiar with VBA macros? In the module called ThisWorkbook, add this code:

Please Login or Register  to view this content.

Users will have to allow macros to run.

[interested]

I tried Alt+F11, but its just blank, i cant see a module called "this work book"...see attached vba.jpg or do i need to do something else....Thanks for the reply so far

[interested]

ive stripped the data out, as there where 9000 lines to the attached work book. could you look at the attached for me?
i'm sorry i dont understand the following


' in this location, select the data to be included on the form, such as
Sheet1.Range("A1:E20").Select

Sheet1.ShowDataForm
End Sub

do i have to re-name sheet1 , the tab name i have??
i am asking as i may use this on other sheets we have.

thanks again

[6StringJazzer]

I don't know why you can't expand the module for that workbook. It should have a plus sign like the others.

See attached and let me know if that works for you. [interested=open data form on workbook open=User form example.xls]

[6StringJazzer]

Also, you don't have to rename worksheets to use this for other purposes, you just have to modify the code. In the code I provided, the expression Worksheets("Log") refers to the name on the tab that the user sees. My original example above refers to the name shown in the code but not visible to a user.

[interested]

yes, your example worked for me, and i understand why the module is not visable. Its a "shared" workbook.In the example i uploaded i just copied the first 3 lines. I took another copy,un shared it and the module was there. Is it posible to have the code on a shared book by some work round??

[JosephP]

you can't edit code in a shared workbook but you can unshare it, add the code and then share it again. as long as the code doesn't violate any of the restrictions of shared workbooks it should be fine

[interested]

Thanks for your input, but it does not save the code on a shared book when i tried. Maybe i will just have to have it un shared as the code is fine.

[JosephP]

you have to save it unshared with the code then share and save it

[interested]

Ah yes. Thankyou all for your help. will marked as solved.