locked
How to use Excel when a user form is open RRS feed

  • Question

  • Hi all,

     

    I have a macro that opens a user form when an excel sheet loads. when this form is open, the workbook is disabled. you cannot scroll down or save etc.

     

    Is there anyway of enabling the scrolling? or the entire workbook?

     

    Ben


    Ben Taliadoros Software Engineer
    Thursday, July 14, 2011 3:01 PM

Answers

All replies

  • Hi all,

     

    I have a macro that opens a user form when an excel sheet loads. when this form is open, the workbook is disabled. you cannot scroll down or save etc.

     

    Is there anyway of enabling the scrolling? or the entire workbook?

     

    Ben


    Ben Taliadoros Software Engineer
    • Merged by Liliane Teng Friday, July 15, 2011 7:41 AM double post
    Thursday, July 14, 2011 3:00 PM
  • I don't think that it's possible to edit the sheet when you have a user form openned on Excel.

    jppinto

    www.excel-user.blogspot.com

    Thursday, July 14, 2011 3:15 PM
  • If you want to allow the user to interact between Excel and your form, show the form as modeless, eg

    UserForm1.Show vbModeless

    Peter Thornton

    • Proposed as answer by evohnave Thursday, July 14, 2011 3:29 PM
    • Marked as answer by BenTal Monday, July 18, 2011 1:38 PM
    Thursday, July 14, 2011 3:16 PM
  • Try calling

    ActiveWorkbook.AcceptAllChanges
    

    (ref http://msdn.microsoft.com/en-us/library/bb209526(v=office.12).aspx)

     

    There is also a method in Worksheet that controls the scroll area

     

    ActiveWorkbook.ActiveSheet.ScrollArea = ""
    

    Will tell it to make scrolling available in entire sheet.

    (ref http://msdn.microsoft.com/en-us/library/bb221614(v=office.12).aspx)

     

    Is this any help?

     

    --Greg

     

    • Marked as answer by BenTal Thursday, July 21, 2011 2:19 PM
    Thursday, July 14, 2011 3:33 PM
  • Yes! Modeless is the way to go! However there is another way.....

    One can control from your form these functions you want to do in excel, scrolling etc...

    This depends on why you need to start the macro upon starting excel? Is this to protect what you do, in that case running modeless is not so good then you will be able to change the contents in the excel sheet. If the reason is NOT to secure the info, then my question is why do you just not remove that macro on start up and instead call it from a button on the menu or directly on the sheet?

    If I misunderstood please clarify!

    Best regards


    Leif
    Friday, July 15, 2011 11:59 PM
  • One more thing, you can just disable the form with a button on your form and when the person has made those changes needed he just press a button on the excel sheet to return to the main userform! This is very easy and also you can disable the person from accessing or closing the excelsheet from that excelsheet view, the user must turn back top the main UserForm to be able to quit Excel, and by doing this he/she also automatically saves the excelsheet!

    I have done several of these kinds of sheets if you want me to I can give you some code!

     

    BR

    Leif


    Leif
    Saturday, July 16, 2011 12:05 AM
  • 'Code to access the sheet from the userform

    Private sub Commandbutton1.click()

    Sheets("XXX").select

    MenuForm.hide

    End sub

    'And on the userform create a button from any object (picture to an inbedded button)

    'then just create a Makro with the code

    Sub runUserMenuForm()

    MenuForm.Show

    End sub

     

    You can also use the code from within:

    ActiveWindow.scrollcolumn=1'scrolls between columns

    Sheets("XXX").visible = true ' This unhides the sheet

    Sheets("XXX").select ' This activates the sheet and by that you can use the code below

    ActiveSheet.Unprotect ' removes the protection of the form/

    ActiveWindow.SelectedSheets.visible=false ' This shows the selected sheet, no need to point to a certain sheet if only this sheet is active and shown/ incase several sheets are avilable you must point on that sheet you want to hide!

     

    I hope this can help you!


    Leif
    Saturday, July 16, 2011 12:30 AM
  • This is exaclty what i wanted, thanks Peter you genius!

     

    jpgpinto, i was once like you!


    Ben Taliadoros Software Engineer
    • Marked as answer by BenTal Monday, July 18, 2011 2:06 PM
    Monday, July 18, 2011 1:45 PM
  • Is this in english?


    Ben Taliadoros Software Engineer
    Thursday, July 21, 2011 2:18 PM
  • Try calling

    ActiveWorkbook.AcceptAllChanges
    

    (ref http://msdn.microsoft.com/en-us/library/bb209526(v=office.12).aspx)

     

    There is also a method in Worksheet that controls the scroll area

     

    ActiveWorkbook.ActiveSheet.ScrollArea = ""
    

    Will tell it to make scrolling available in entire sheet.

    (ref http://msdn.microsoft.com/en-us/library/bb221614(v=office.12).aspx)

     

    Is this any help?

     

    --Greg

     

    genius
    Ben Taliadoros Software Engineer
    Thursday, July 21, 2011 2:19 PM
  • Open the UserForm as a Modeless form and change the ShowModal Property of UserForm to False
    Sunday, October 30, 2016 12:55 PM