Open a hidden sheet with clicking a button

Hey All,

I have a workbook including several sheets.

I'd like to hide all worksheets and in the excel there is only one visible sheet which is Navigation Page. I want to add several buttons on this sheet and each button will be clicked to call the other sheets.

For example, there is a sheet called Sales_Approval_Form and I'd like to add a button on navigation page called Sales Approval Form and when the user clicks the button the sheet will be open for entering data.

So, Could you help me on this?

Thanks,

Emre

Thanks for your reply. It worked with the codes.

I have another issue with this. Now a user can click the button to open the hidden sheet and is able to fill the form. After filling, I need to have an export file button to save the file as another excel workbook to a folder that user wants. There is also a button to empty the fields(I mean refresh the form) which are entered by user.

And also on the navigation page there are one more button which are used to call the existing document where the user save as I mentioned above.

I'll be glad if you can help on those?

Thanks...

1)

Please Login or Register  to view this content.

3)

Please Login or Register  to view this content.

2)
you may use Clear or ClearContents method, if you mean a range...

example:

Please Login or Register  to view this content.

Thank you very much for your help.

I have tested and it worked fine. I just checked the functionality as a whole and let you know if I need assistance.

By the way, how can I changed the 1st code you wrote for saving as just the sheet opened instead of whole workbook.

is it work like this?

Worksheets("Sales").SaveCopyAs fPath & fName & ".xlsm"

Thanks

you are welcome.

try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Hi again,

Is is possible to open only one sheet (Navigation Page, for instance) when the excel file is open?

I did all thing you wrote, but after I close the excel, it will be normally open as I left. What I want is that when a user close the excel and open the same file again, he/she will see only the navigation page and the other sheets are hidden.

Can you help me on this?

Emre

Yes you can open only one sheet you need.

Assume that Sheet1 is the sheet which you need to visible and rest all invisible.

Please try below code in Workbook Open Event

Please Login or Register  to view this content.

___________________________________________________________________________________________________________________________________
Please click * to say thank you

Yes you can open only one sheet you need.

Assume that Sheet1 is the sheet which you need to visible and rest all invisible.

Please try below code in Workbook Open Event

Please Login or Register  to view this content.

___________________________________________________________________________________________________________________________________
Please click * to say thank you

Thanks, it is working...

By the way, everytime I click the save button for workbook, it always opens the "Save As" window with a different name. And also, everytime I have to choose macro enabled format to save.

My workbook names as Business Case, when I tried to save, it adds rename as BusinessCase1 and ask me to choose file format etc.

Why does it happen like this?

I saved as a macro enabled template each time, that's why I got a renamed file name

Hey All,

Below Code works for exporting the file, but when I select cancel from the brows window after clicking the export file button, system also opens the xlsx file which I want to export as well. I want it to do nothing when the user clicks cancel.

How can I modify the code?

Sub TWB_SaveCopyAs()

Dim fPath As String, fName As String

fName = Application.UserName 'this is an example. change to suit your needs.

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
fPath = .SelectedItems(1)
End If
End With

ThisWorkbook.SaveCopyAs fPath & fName & ".xlsm"

End Sub