Create a home button in an excel workbook that was generated by vba

[rofin]

Guys,


I have a userform that view the data contents in another new workbook. What i cannot do is how to put button saying "go back" and once clicked it will close the new book and not saving it and then goes back to my main userform. thaks for any help

 

[Yongle]

Re: Pls. Help: How to create a home button in an excel workbook that was generated by vba

It could be as simple as

ActiveWorkbook.Close False

But that risks that wrong workbook being active when the code runs, so it is safer to refer to the actual workbook you want to close

What line of code are you using to open the "new workbook"

thanks

 

[rofin]

Re: Pls. Help: How to create a home button in an excel workbook that was generated by vba

Hi Yongle,

Thanks. I think i did not make my question so clear, Let me ask it again in this manner

1. In the Main UserForm there is a button wherein it generates a new excel workbook with the contents of my original sheet.( to view the inventory list in a new workbook)
2. After the user review the generated excel workbook and wants to go back to my main UserForm and do something else the user will have to close the generated excel and since the purpose is for viewing only the user would not save the workbook generated since the original data is safe inside my original excel file.
3. I would like to a have a button in the generated excel workbook for the user to just click it to go back to my main UserForm. (once click the generated excel will close and directed the user to my main userform.)

 

[Yongle]

Re: Pls. Help: How to create a home button in an excel workbook that was generated by vba

I did understand what you wanted
- but you did not provide the line of code that opened (or in this case) created the new workbook
- hopefully easy enough to dovetail my code into yours..

1. Set ShowModal property of userform to FALSE

2. At top of a standard module to make this variable available to ALL modules

Public [B]wb[/B] As Workbook

3. the code that creates the workbook and copies the sheet needs reference to wb

    Dim ws1 As Worksheet: Set ws1 = Sheets("MyOriginalSheet")
    Set [B]wb[/B] = Workbooks.Add
    ws1.Copy before:=[B]wb[/B].Sheets(1)

4. to close the workbook again needs reference to wb - if macro attached to a shortcut it is easier to run from the temporary workbook

Sub CloseIt()
    [B]wb[/B].Close False
End Sub

5. In ThisWorkbook module - to hide the form when looking in other workbook
- again note declaration of variable at top of this module which makes the variable available to all procedures in the module

Dim ufHidden As Boolean

Private Sub Workbook_Activate()
    If ufHidden = True Then
        UserForm1.Show
        ufHidden = False
    End If
End Sub

Private Sub Workbook_Deactivate()
  UserForm1.Hide
  ufHidden = True
End Sub

 

[gselvam]

Re: Pls. Help: How to create a home button in an excel workbook that was generated by vba

Hi

Are u looking for.....like Main sheet to Other sheet.....From other sheet you want to go to Main sheet.
IF so in each sheet ...have one cell as 'MAIN' and keep hyperlink to main sheet.
It will work