Open a worksheet through a userform command button click

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
I have a macro based excel workbook which opens in a userform with a command button on it.
I want to open a worksheet in this workbook by clicking the command button.
The userform is closes but sheet does not open. However if the same workbook is again clicked, a message is displayed that "file is already open , do you want to open again" . I tried following codes but the sheet does not open.
what improvement to be done in the code. please guide.


VBA Code:
Private Sub UserForm_Initialize()

    With Application
    .WindowState = xlMaximized
    Zoom = Int(.Width / Me.Width * 85)
    Width = .Width
    Height = .Height

End With

End Sub

Private Sub cmdWelcome_Click()
         
ThisWorkbook.Sheets("Calender").Visible = True

ThisWorkbook.Sheets("Calender").Select
       

End Sub

Private Sub UserForm_Terminate()

     frmBackground.Show
     ActiveWorkbook.Save

    ActiveWorkbook.Close
    Application.Quit

End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
Please explain better how many userforms and how many workbooks are involved in your process, which form belong to which workbook, and which workbook the sheet "Calender" belongs to.

Bye
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
I have a main userform "frmWelcome" in which the program opens up, it has a command button "cmdWelcome", which should open up / show the sheet "Calender".
besides this i have two forms - one userform "userform1" for data input and other form "frmBackground" which has logout button.

I want the command button on userform "frmWelcome" to show the sheet "Calender" upon clicking.

thanks in advance

happy new year in advance
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
But you didn't clarify which workbooks are involved:
-which workbook contains "frmWelcome"?
-is it the same workbook that contains the sheet "Calender"?
In case the answer to this second question is Yes, then I think you should just remove the Sub UserForm_Terminate, as it closes the workbook you would like to display and (habit quite bad) even the Excel application

Bye
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

sorry i did not answer fully. it contains only one workbook as you pointed out.

i had already tried deleting this Sub Userform_terminate also but the sheet opens in the background as minimised one as I said earlier.
is the code for displaying sheet correct? neither of these two is helping me so that the sheet opens in maximised way.


VBA Code:
 Private Sub cmdWelcome_Click()
ThisWorkbook.Sheets("Calender").Visible = True

ThisWorkbook.Sheets("Calender").Select[
 frmWelcomeNew.Hide
 End Sub /CODE]


or this 

[CODE] Private Sub cmdWelcome_Click()
ThisWorkbook.Sheets("Calender").Activate[
 frmWelcomeNew.Hide
 End Sub/CODE]
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
"In general" the code seem correct.
However before you continue developing your interface you need to keep in mind that Microsoft says:
"When a UserForm is modal, the user must respond before using any other part of the application"
(see for example Show method (Visual Basic for Applications))

In other words you cannot modify the "Application" (i.e. Excel environment) while the form is open.
I have seen situation in which after the form be closed a sheet selected by the userform code were shown on the screen but the focus was still on the original sheet (yes! I was looking at sheetA but if typed something it went to sheetB!).

I suggest that you open the userform in vbmodeless
VBA Code:
frmWelcome.Show vbModeless
In this way, however, the user is not forced to stay on the userform

The Sub UserForm_Terminate code is anyway to be removed (or changed) because it's nonsense to close the workbook you are working on (not to mention quitting the Application, i.e. closing Excel)

Bye
 
Solution

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
thanks Mr Anthony .... it worked...
thanks for small tips which make wonders.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top