0

I have created a userform to add data in order to generate a report and multiple users will have access to the file containing the userform.However my problem is that everytime i open the ".xlsm" file there are instances where the excel workbook in the background is visible.Also i'm having problems when the userform initiates.I looked for the solution to this on the internet and got a lot many ideas.However,i still did not get what i was looking for and thus am asking the question.

I have tried the following code;

Private Sub Workbook_Open() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.WindowState = xlMinimized ActiveWindow.WindowState = xlMinimized 'Also tried the following '1 'Application.Visible = False(Have error running the code if i use this. It worked fine in excel 2007 but in 2013 and 2016 and higher versions the code won't work properly.No idea why.) '2 ' With Application (This was basically to hide the visible excel application window behind the userform)` ' .WindowState = xlNormal ' .Height = 500 ' .Width = 300 ' End With 'do stuff (Cells.ClearContents) userform1.show End Sub

I also tried creating and running a ".vbs" file but, it seems to avoid only the excel startup splash screen.

Set objExcel = CreateObject("Excel.Application") objExcel.Visible = false Set objWorkbook = objExcel.Workbooks.Open("C:\Users\filepath\filename.xlsm")

Following are a few screenshots that might help you understand the problem better.

1: On startup the userform always opens up behind my current window.Is there a way to fix this? enter image description here

2: Because of the first problem the user usually tends to click on the excel application icon on the task bar. enter image description here

3: Thus making the workbook/worksheet visible in the background like so.I want to avoid this from happening.No matter what i want the user to see only the userform .Is there something i'm doing wrong? enter image description here

PS. 1: I also saw that one can put a background image of one's choice to avoid showing the excel worksheet.But since i'm fairly new to vba i really don't know if that would be the best option and haven't tried it yet.I'd prefer tweaking the current code i have (if possible) to get the desired result.

Improve this question
3
  • @braX I have no idea about VB.NET but i'll look into it.Thanks! Commented Sep 20, 2019 at 7:20
  • @JvdV I’d really appreciate it if you’d help me with a solution if it’s possible. Commented Sep 20, 2019 at 7:34
  • stackoverflow.com/questions/49950271/… Commented Sep 20, 2019 at 7:46

1 Answer 1

Reset to default
1

In Excel 2019, the following worked for me:

  • Attached the following to the Workbook_Open event:

    Private Sub Workbook_Open() Application.Visible = False UserForm1.Show End Sub

  • Created a UserForm with a CommandButton where I did put the following:

    Private Sub CommandButton1_Click() Application.Visible = True Unload Me End Sub

Obviously you can use these codes at many more places, but this was just for test purposes:

enter image description here

Edit:

If this does not automatically bring Excel to the forefront, you might want to consider using:

AppActivate Application.Caption

Put that as a first line. Testing this threw an "error 5" to me but looking around the net this is definately not the case to all users. While this is a rare occurence, you can tackle that problem (if you have it) by implementing a waiting time (as that helped me):

Dim HoldOn As Date HoldOn = DateAdd("s", 10, Now()) Do While Now < HoldOn Loop

Lower the waiting time if you can as 10 seconds might be a bit much. The whole thing then looks like:

Private Sub Workbook_Open() Dim HoldOn As Date HoldOn = DateAdd("s", 10, Now()) Do While Now < HoldOn Loop AppActivate Application.Caption Application.Visible = False UserForm1.Show End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@ JvdV used the same code imgur.com/a/LS0oumx Image of the code i have. imgur.com/IrQNAVy
@Sw1tch, maybe my edits helped you out. It works on my end.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.