0

I have a userform which is created in vba and I want to display only the userform when I open my excel... is there a way to do this.. I have already tried the codes such as

application.visible = false , activewindow.visible= false

if I use this codes in the module before open the files which are already open will b hidden along with the file which I am opening can someone tel me how can i particularly hide the file which I want to open and display the userform

Shahbaz Shaikh
  • 21
  • 1
  • 1
  • 6
  • Did you try putting `application.visible = false` in form initialisation event? – Pankaj Jaju Nov 16 '17 at 09:24
  • the problem is if i use application.visible code it hides all the files which are opened in excel .... for example if i m working on a excel data sheet n if i open this userform it hides the datasheet too along with this – Shahbaz Shaikh Nov 16 '17 at 10:08
  • Check if the sample code in the answers section works for you. – Pankaj Jaju Nov 16 '17 at 10:44
  • I have already tries this ...it hides the window but the background of excel vl stil be there if I open my file which contains the userform – Shahbaz Shaikh Nov 16 '17 at 11:09
  • I think, that this is impossible with pure VBA, but [possible](https://stackoverflow.com/questions/24733377/excel-useform-how-to-hide-application-but-have-icon-in-the-taskbar) with Windows API functions. – CommonSense Nov 16 '17 at 11:28
  • can u give me an example with api functions ?? @CommonSense – Shahbaz Shaikh Nov 16 '17 at 11:52
  • 1
    Possible duplicate of [Excel Useform: How to hide application but have icon in the taskbar](https://stackoverflow.com/questions/24733377/excel-useform-how-to-hide-application-but-have-icon-in-the-taskbar) – CommonSense Nov 16 '17 at 15:12

2 Answers2

5

Try something like this

1- Create a user form with 2 button (see below pic)

enter image description here

2- ThisWorkbook code

Private Sub Workbook_Open()
    UserForm1.Show vbModeless
End Sub

3- Form code

Private Sub CommandButton1_Click()
    If Workbooks.Count > 1 Then
        Windows("Test.xlsm").Visible = True
    Else
        Application.Visible = True
    End If
End Sub

Private Sub CommandButton2_Click()
    If Workbooks.Count > 1 Then
        Windows("Test.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
End Sub

Private Sub UserForm_Initialize()
    If Workbooks.Count > 1 Then
        Windows("Test.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
End Sub

Private Sub UserForm_Terminate()
    If Workbooks.Count > 1 Then
        Windows("Test.xlsm").Visible = True
    Else
        Application.Visible = True
    End If
End Sub

This will only show or hide the form's workbook. Any other workbooks opened will remain unaffected.

Pankaj Jaju
  • 5,371
  • 2
  • 25
  • 41
  • but if i open only one excel which is having the userform then the background of the excel vl b there .... – Shahbaz Shaikh Nov 16 '17 at 11:11
  • @ShahbazShaikh - Updated the code ... basically you have to check the number of workbooks in order to show or hide the entire application or just the workbook. – Pankaj Jaju Nov 16 '17 at 11:33
  • the bug I found is the other excel file pop ups in background of userform when workbook.count >1 – Shahbaz Shaikh Nov 16 '17 at 12:28
  • Which is what you want I assumed? You said my first code hides all workbooks and you only wanted to hide the form workbook. – Pankaj Jaju Nov 16 '17 at 12:44
  • correct in the first code of urs all workbooks wer hiding but the excel template background was visible... in this code if any other excel file is opened it pop ups behind the userform.... how can I see only userform then... u can check the code by parallel opening 2 excel sheets ..... – Shahbaz Shaikh Nov 16 '17 at 13:22
  • v need to minimize the other workbooks when workbook >1 ...for that there should be a proper if n else condition – Shahbaz Shaikh Nov 16 '17 at 13:24
  • I hope u understood wat problem I m facing @ pankaj jaju – Shahbaz Shaikh Nov 16 '17 at 13:24
  • we need to use ActiveWindow.WindowState = xlMinimized after Windows("Test.xlsm").Visible = True then I guess it might work fine .... – Shahbaz Shaikh Nov 16 '17 at 13:30
  • Not sure what you want - What to show when only test.xlsm is opened? And what should be displayed when 2 or more workbooks including test.xlsm are opened? – Pankaj Jaju Nov 16 '17 at 13:39
  • when test.xlsm is opened I want the userform which is in test.xlsm to be opened hiding the excel application...i.e userform vl only b seen n the excel file vl b backend..... now when two or more workbooks are opened the result I want is the same i.e the display of only userform which is in test.xlsm but when I used the above code the text.xlsm gets hide and the remaining worksheet which is minimized gets popup at the back of the userform of test.xlsm.... – Shahbaz Shaikh Nov 16 '17 at 13:50
  • Which is what I coded initially where I used Application.Visible to show and hide the workbooks but you said you want other workbook to be displayed – Pankaj Jaju Nov 16 '17 at 13:56
  • if we use application.visible code it only works when test.xlxm is the only file I m using .... but if there are multiple files which I m parallel working then application.visible vl hide al the files.... I want only test.xlsm file to b hidden when using multiple files and rest of the files should b in a minimized state ...which makes only userform appear on the front and test.xlxm vl backend and rest of the files are minimized ... – Shahbaz Shaikh Nov 16 '17 at 14:05
  • If other workbooks are minimized, you will still see the Excel blank window as all workbooks might be opened in the same instance of Excel. – Pankaj Jaju Nov 16 '17 at 14:47
0

I think if you ensure that other workbooks are not open, that might solve the problem.

Private Sub Workbook_Open()
 If Workbooks.Count > 1 Then
   MsgBox "Close All excel files before running it"
   ThisWorkbook.Close
 Else
    Application.Visible = False
    frmmain.Show
 End If
End Sub
Deb
  • 121
  • 1
  • 1
  • 12