locked
Want to select sheet in Macro that is run from a userform command button (Excel 2013) RRS feed

  • Question

  • I have code to select a sheet, the macro is run when a commandbutton on a userform is clicked, but afterwards the new sheet is selected but I can't enter data on that sheet.  I try to enter data but it gets put on the previous sheet.
    Want to select sheet in Macro that is run from a userform command button (Excel 2013)

    Private Sub CommandButton1_Click()
      Call SelectSheet1
      Unload UserForm1
    End Sub
    Sub SelectSheet1()
       Sheets("Sheet1").Activate
     End Sub
    I also have link to file and video if you need to see error for yourself.  I do not get the error in Excel 2007 or if I run the macro directly from developer tab.

    Any help or ideas of a workaround would be appreciated.



    Thanks




    Saturday, February 28, 2015 10:37 AM

Answers

  • I was able to figure out how to get it to work, but not sure why this solves the issue.

    Im unloading the Userform before call macro and I tried using select instead of Activate, those did not help.  But after I switched so that the UserForm loads with vbModeless then my problem went away, not sure why this fixed it.

    Sub ShowMainMenu()
        
    UserForm1.Show vbModeless
    
    End Sub
    Sub SelectSheet1()
    
    Sheets("Sheet1").Select
    
    End Sub

    For me the key to fixing this issue was vbModeless

    Monday, March 2, 2015 7:46 PM

All replies

  • Hello Dar,

    How do you show the form? Is it a non-modal form?

    The Activate method of the Worksheet class makes the current sheet as active. Calling this method is equivalent to clicking the sheet's tab. I don't see anything strange in this code. But I'd suggest changing the order of calls in the event handler:

    Private Sub CommandButton1_Click()
        Unload UserForm1
        Call SelectSheet1
    End Sub

    Saturday, February 28, 2015 11:07 AM
  • Hi,

    Normally you to select a sheet sheet below code for example:

    sheets("Sheet1").select
    

    When youu use a userform you use the form to show, for example:

    frmTest.show

    When you use the buttons in a form, you need to refer to "me", for example:

    Private Sub cmdCancel_click()
    
    me.hide
    sheets("sheet1").select
    
    End Sub

    The "me" refers to the userform, therefore it will hide your userform.

    Hope this helps.

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    Saturday, February 28, 2015 11:19 AM
  • Yes,

    I tried changing the order and it did not make a difference

    Saturday, February 28, 2015 5:11 PM
  • Yes,  I do have code to show my userform, but I did not include it above as I do not have a problem with the user form opening.

    Sub ShowMainMenu()
        
    With UserForm1
      .Show
    End With
    
    End Sub


    • Edited by Dar VBA_MBA Saturday, February 28, 2015 5:14 PM
    Saturday, February 28, 2015 5:14 PM
  • Hi,

    I can’t reproduce that issue, please try it in a new to file to check whether it has the same issue. You could share a sample file on the OneDrive.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 2, 2015 5:51 AM
  • I tried in a new file and am having the same issue but I noticed that when I test the userform in debug window it does not have the problem,  but when I click a shape on a worksheet that shows the userform then I am having the problem.

    This video link shows the problem more clearly and has to link file which I also have below.

    https://www.youtube.com/watch?v=p34IbXS98Xs

    https://www.dropbox.com/s/yh864yfoeoaijn0/Test%20Dialog%20Button%20for%20Navigation.xlsm?dl=0

    Monday, March 2, 2015 7:26 PM
  • I was able to figure out how to get it to work, but not sure why this solves the issue.

    Im unloading the Userform before call macro and I tried using select instead of Activate, those did not help.  But after I switched so that the UserForm loads with vbModeless then my problem went away, not sure why this fixed it.

    Sub ShowMainMenu()
        
    UserForm1.Show vbModeless
    
    End Sub
    Sub SelectSheet1()
    
    Sheets("Sheet1").Select
    
    End Sub

    For me the key to fixing this issue was vbModeless

    Monday, March 2, 2015 7:46 PM
  • Hi,

    Based on my test, I found that it has the issue with assigned macro shape, it works fine if we are using ActiveX controls (Command button).

    Your solution is ok, for the parameter of vbModeless, it doesn’t display the userform as model box and we can do other action in the sheet.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 3, 2015 6:40 AM