0

I want to make a GUI-like interface for an Excel workbook, and I don't want the workbook to be visible until it closes. However, making the workbook not visible messes with the references of my code and I cannot read the ranges without heavily modifying it. I tried minimizing the window, but that minimizes the form as well.

Is there a way to keep the active workbook active but not readily visible, and the form visible?

Improve this question
5
  • If this happens: "However, making the workbook not visible messes with the references of my code and I cannot read the ranges without heavily modifying it." Then you are probably using .Select or .Activate in your code. This is a bad practice and is highly recommended to be avoided (many undefined issues and slow code). If this is the case google for how to avoid using them both, there are many tutorials. Yes, removing them can be a lot of work, but saves you much time in future debugging and makes your code a lot better. Commented Sep 11, 2017 at 15:10
  • I would have thought there's no need to keep the workbook active - can't you just reference the workbook/sheet using a variable? This works fine for me and I can still reference any sheet on any workbook I like: Private Sub Workbook_Open(): Application.Visible = False: UserForm1.Show: End Sub Commented Sep 11, 2017 at 15:13
  • @DarrenBartrup-Cook Yes, that's what I said. Don't use .Select and .Activate instead reference the worksheet directly like Worksheets("MySheetName").Range("A1"). But I think because he said hiding the sheet messes up his references he is using .Select and he has to change that to not using .Select and reference directly. Then the references would still work even after hiding the workbook. Commented Sep 12, 2017 at 6:03
  • Yeah sorry, think I wrote my comment at the same time as you. After checking my little bit of code did what I said it did you'd already posted, but didn't want to waste the 5 minutes it took to test my code so posted anyway. :) Commented Sep 12, 2017 at 7:40
  • @DarrenBartrup-Cook Ahh, no need to be sorry, I thought you meant me with your comment not him haha. That was confusing me. Now that makes more sense that this comment was meant for Viriax. Commented Sep 12, 2017 at 9:26

2 Answers 2

Reset to default
2

Include the following when you load the userform

Private Sub UserForm_Initialize() ThisWorkbook.Application.Visible = False End Sub

and this for when you end the userform (return to normal state)

Private Sub UserForm_Terminate() ThisWorkbook.Application.Visible = True End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks! This worked for me. However, I had to change it to Application.Visible = True, if I left "ThisWorkbook." it would not reappear.
-1

put this code in form's UserForm_Initialize method

ActiveWindow.Visible = False
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.