+ Reply to Thread
Results 1 to 12 of 12

Userform opens in another worksheet

  1. #1
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Userform opens in another worksheet

    Hi

    my excel has 3 worksheets. In sheet1 there are few buttons and one of them opens a userform.
    but whenever I click on that button it opens sheet3 and then opens the userform, so you can see the worksheet in the background.

    Can someone say what's causing this problem and How can I change that?

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Userform opens in another worksheet

    Somehow your code for the button is activating/selecting sheet3. try entering a line of code in the button execute code to activate/select sheet1 or sheet2

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Userform opens in another worksheet


    Can someone say what's causing this problem and How can I change that?


    Not without seeing the code which runs when you click on the button


    Regards,

    Greg M

  4. #4
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: Userform opens in another worksheet

    This is what I use to open the userform:
    Please Login or Register  to view this content.
    I used this to create a ComboBox, Maybe this is the problem?

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Userform opens in another worksheet

    Hi again,

    The code you posted for the "UserForm_Activate" event doesn't seem to select/activate any other worksheet.

    My next request is to see whatever code is included in the "UserForm_Initialize" routine. Can you post that code here please?

    Regards,

    Greg M

  6. #6
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: Userform opens in another worksheet

    Quote Originally Posted by Greg M View Post
    Hi again,

    The code you posted for the "UserForm_Activate" event doesn't seem to select/activate any other worksheet.

    My next request is to see whatever code is included in the "UserForm_Initialize" routine. Can you post that code here please?

    Regards,

    Greg M
    Hi

    This is the UserForm_Initialize:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Userform opens in another worksheet

    I think it would save a lot of time if you could post the workbook.
    Rory

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Userform opens in another worksheet

    Hi again,

    There doesn't appear to be anything in your "UserForm_Initialize" routine which would cause the active worksheet to change.

    I agree with rorya

    We probably don't need to see any of the data in your workbook, so if there's anything sensitive in it you can start by just posting all of your VBA code.

    Regards,

    Greg M

  9. #9
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: Userform opens in another worksheet

    Yeah you are right! Unfortunately, I can't upload my file. sorry

  10. #10
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: Userform opens in another worksheet

    Quote Originally Posted by Greg M View Post
    Hi again,

    There doesn't appear to be anything in your "UserForm_Initialize" routine which would cause the active worksheet to change.

    I agree with rorya

    We probably don't need to see any of the data in your workbook, so if there's anything sensitive in it you can start by just posting all of your VBA code.

    Regards,

    Greg M
    Hi

    thanks for the response.
    I went through my codes and found this code in all comboboxes:
    Please Login or Register  to view this content.
    I change them to
    Please Login or Register  to view this content.
    and changed the other lines accordingly.

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Userform opens in another worksheet

    Hi again,

    Thank you for that feedback.

    One VERY important point!

    Please Login or Register  to view this content.
    will activate whichever worksheet has the VBA CodeName of "sheet3",

    whereas

    Please Login or Register  to view this content.
    will create a reference to whichever worksheet happens to be associated with the third tab of the workbook.


    From a code point of view, the VBA CodeName of a worksheet is MUCH more "robust" than its TabName or its position within the workbook - VBA CodeNames are very unlikely to be changed by Users, but Users can quite easily alter the order of the worksheets or their TabNames if the workbook structure is not password-protected.


    More significantly, in the absence of further information, it would appear that the code you've just posted can be omitted. If the CommandButton which displays the UserForm is located on the worksheet you want to display "underneath/behind" the UserForm, that worksheet will, by definition, be the active worksheet when the UserForm is displayed.


    Hope you find this useful/interesting.

    Regards,

    Greg M


    P. S. Many thanks for the Reputation increase - much appreciated!
    Last edited by Greg M; 09-24-2020 at 01:12 PM. Reason: P. S. added

  12. #12
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: Userform opens in another worksheet

    Quote Originally Posted by Greg M View Post
    Hi again,

    Thank you for that feedback.

    One VERY important point!

    Please Login or Register  to view this content.
    will activate whichever worksheet has the VBA CodeName of "sheet3",

    whereas

    Please Login or Register  to view this content.
    will create a reference to whichever worksheet happens to be associated with the third tab of the workbook.


    From a code point of view, the VBA CodeName of a worksheet is MUCH more "robust" than its TabName or its position within the workbook - VBA CodeNames are very unlikely to be changed by Users, but Users can quite easily alter the order of the worksheets or their TabNames if the workbook structure is not password-protected.


    More significantly, in the absence of further information, it would appear that the code you've just posted can be omitted. If the CommandButton which displays the UserForm is located on the worksheet you want to display "underneath/behind" the UserForm, that worksheet will, by definition, be the active worksheet when the UserForm is displayed.


    Hope you find this useful/interesting.

    Regards,

    Greg M


    P. S. Many thanks for the Reputation increase - much appreciated!
    Thank you for explaining this.
    I'm pretty sure that the Tab-names are going to change after I'm finished with the username.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform thats automatically opens
    By Bech2912 in forum Excel General
    Replies: 4
    Last Post: 10-12-2012, 05:35 PM
  2. [SOLVED] Userform to Start When Workbook Opens
    By JP777 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-06-2012, 10:04 PM
  3. ? set focus to a textbox when userform opens
    By madhg in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-30-2009, 04:54 AM
  4. Only Display Userform when workbook opens
    By royUK in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2009, 04:35 PM
  5. Only Display Userform when workbook opens
    By karthikcoep in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2009, 03:39 PM
  6. Controlling Location Userform Opens
    By googlebot in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2008, 01:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1