Excel Opens to Userform and then shuts off

[sassriverrat]

Hello All-

Everyone has been fantastic helping me build (and learn) VBA on this new workbook. It's been great- now I've run into an issue that i don't readily see.

I have 5 userforms. Userforms 1, 2, and 3 open (Workbook_Open) based on one of three names of the workbook- Irregardless of which userform is used, they all have a button to choose "Developer" which opens Userform 5. Userform 5 has two buttons, one will ask for a password to show sheet1, the other just shows sheet2 (no password).

Coding for userform 5 is shown below. It seems when i open excel, the green mini excel window opens, loads, then opens the main window (workbook) for half a second before going to userform1(based on name- does this for any of the first 3 userforms). Once I click the appropriate button, the next userform will open (code below) and after selecting EITHER of its buttons, the workbook flashes behind again for a second and then excel is gone. Any ideas why excel is gone? Any way I can also stop the main workbook window from opening for that brief second before the first userform opens.

Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("Ports").Activate
Sheets("Notes").Visible = True
Application.Visible = True
Unload Me
End Sub


Private Sub CommandButton2_Click()
mypass = Application.InputBox("Enter Password", "The Coding Sheets")
If mypass = "1234567890" Then
Sheets("Developer").Visible = True
Sheets("Notes").Visible = True
Application.Visible = True
Unload Me
End Sub

 

[Norie]

Where's the code that opens the initial userforms?

 

[sassriverrat]

Private Sub Workbook_Open()
Application.Visible = False 
If Activeworkbook.name = “Master Voyage Report.xlsm” Then
  Userform1.Show
  Application.Visible = False
ElseIf Activeworkbook.name = “Current Voyage Report.xlsm” Then 
  Userform2.Show 
  Application.Visible = False 
Else: Userform3.Show 
  Application.Visible = False 
 End If 
End Sub

 

[sassriverrat]

Anyone? @Norie?

 

[NdNoviceHlp]

Here's my guess... re-arrange the .visible and .show (ie. make wb invisible then show userform). Why use activate? Why activate 1 sheet then make another sheet visible? Also I think the application has to be visible before making the sheet visible. Norie probably has a better solution?HTH. Dave

 

[sassriverrat]

I tried rearranging to no avail. After the last userform closes the screen flashes the workbook and then closes. If I double click to open the file again it skips the user forms and opens right to the last sheet.

As for selecting- because I want some sheets to be able to be seen but still want the “focus” of the user to start elsewhere. Basically a “you can see the coding if you want but check your inputs before you go after the coding.”

 

[NdNoviceHlp]

Where is the code for UF 1,2 & 3 for showing UF #5 ? Are you sure the file is closed or is it just invisible? Go to the task manager to find out. This part of your code is missing the "End if"....

Private Sub CommandButton2_Click()
mypass = Application.InputBox("Enter Password", "The Coding Sheets")
If mypass = "1234567890" Then
Application.Visible = True
Sheets("Developer").Visible = True
Sheets("Notes").Visible = True
Unload Me
End if
End Sub

Dave

 

[sassriverrat]

Sorry- the End If was in my coding- I was typing my code on my phone...

I've attached the first Five userforms (4 is just an extra) as well as the "workbook_open" piece. thanks for looking!

Workbook_Open

Private Sub Workbook_Open()

'This will show the Userform 1 when starting and hide the excel sheet in the background
    Application.Visible = False
    If ActiveWorkbook.Name = "Master Voyage Report.xlsm" Then
        UserForm1.Show
        Application.Visible = False
     ElseIf ActiveWorkbook.Name = "Current Voyage Report.xlsm" Then
        UserForm2.Show
        Application.Visible = False
    Else: UserForm3.Show
        Application.Visible = False
    End If
End Sub

Userform1

Private Sub CommandButton1_Click()
    Application.Visible = False
    Call VoyageSpecifics
    UserForm4.Show
    Unload Me
End Sub


Private Sub CommandButton3_Click()
    UserForm5.Show
    Unload Me
End Sub

userform 2

Private Sub CommandButton1_Click()
'Adds Noon vs Noon# based on New or Current Voyage
    If ActiveWorkbook.Name = "Master Voyage Report.xlsm" Then
        Call AddNoonSheet
        Sheets("Noon").Select
     ElseIf ActiveWorkbook.Name = "Current Voyage Report.xlsm" Then
        Call AddNoonsSheet
        ActiveSheet.Select
    Application.Visible = True
    End If
    Unload Me
    
    
End Sub


Private Sub CommandButton2_Click()
     Application.Visible = True
     Call ArrivalSheetMaker
        Sheets("Arrival").Select
    Unload Me
    
End Sub


Private Sub CommandButton3_Click()
    UserForm5.Show
    Unload Me
End Sub

Userform3

Private Sub CommandButton1_Click()
    Application.Visible = True
    Sheets("Voyage Specifics").Select
    Unload Me
End Sub


Private Sub CommandButton2_Click()
    UserForm5.Show
    Unload Me
End Sub

Userform4

Private Sub ComboBox1_Change()
    Sheets("Voyage Specifics").Range("C5").Value = ComboBox1.Value
End Sub
Private Sub ComboBox2_Change()
    Sheets("Voyage Specifics").Range("C8").Value = ComboBox2.Value
End Sub
Private Sub CommandButton1_Click()
    'Determines Ballast Condition
    Sheets("Voyage Specifics").Range("F7:H9").FormulaR1C1 = "'Ballast"
    Sheets("Voyage Specifics").Range("F7:H9").Interior.Color = 255
    CommandButton1.BackColor = vbGreen
    CommandButton2.BackColor = &H8000000F
End Sub
Private Sub CommandButton2_Click()
    'Determines Loaded Condition
    Sheets("Voyage Specifics").Range("F7:H9").FormulaR1C1 = "'Loaded"
    Sheets("Voyage Specifics").Range("F7:H9").Interior.Color = 5287936
    CommandButton2.BackColor = vbGreen
    CommandButton1.BackColor = &H8000000F
End Sub
Private Sub CommandButton3_Click()
    'Determines Capt 1
     Sheets("Voyage Specifics").Range("F16:H18").FormulaR1C1 = "'Exact Route Calculator Disabled"
     Sheets("Voyage Specifics").Range("F16:H18").Interior.Color = 255
     CommandButton3.BackColor = vbGreen
     CommandButton4.BackColor = &H8000000F
End Sub
Private Sub CommandButton4_Click()
    'Determines Capt 2
     Sheets("Voyage Specifics").Range("F16:H18").FormulaR1C1 = "'Exact Route Calculator Enabled"
     Sheets("Voyage Specifics").Range("F16:H18").Interior.Color = 5287936
     CommandButton4.BackColor = vbGreen
     CommandButton3.BackColor = &H8000000F
End Sub
Private Sub CommandButton5_Click()
Application.Visible = False
    Unload Me
    UserForm2.Show
End Sub
Private Sub TextBox1_Change()
    Sheets("Voyage Specifics").Range("C4").Value = TextBox1.Value
End Sub
Private Sub TextBox2_Change()
Sheets("Voyage Specifics").Range("C13").Value = TextBox2.Value
End Sub
Private Sub TextBox3_Change()
Sheets("Voyage Specifics").Range("C7").Value = TextBox3.Value
End Sub
Private Sub TextBox4_Change()
Sheets("Voyage Specifics").Range("C8").Value = TextBox4.Value
End Sub
Private Sub TextBox5_Change()
Sheets("Voyage Specifics").Range("C10").Value = TextBox5.Value
End Sub
Private Sub TextBox6_Change()
Sheets("Voyage Specifics").Range("C11").Value = TextBox6.Value
End Sub
Private Sub TextBox7_Change()
Sheets("Voyage Specifics").Range("C12").Value = TextBox7.Value
End Sub
Private Sub Userform_Initialize()
    Me.CommandButton3.Caption = ThisWorkbook.Sheets("Notes").Range("K18") & " " & ThisWorkbook.Sheets("Notes").Range("L18")
    Me.CommandButton4.Caption = ThisWorkbook.Sheets("Notes").Range("K19") & " " & ThisWorkbook.Sheets("Notes").Range("L19")
    ComboBox1.List = [Ports!B3:B52].Value
    ComboBox2.List = [Ports!B3:B52].Value
End Sub

Userform5

Private Sub CommandButton1_Click()
Application.Visible = True
ThisWorkbook.Sheets("Ports").Activate
Sheets("Notes").Visible = True
Sheets("Notes").Select
Unload Me
End Sub


Private Sub CommandButton2_Click()
mypass = Application.InputBox("Enter Password", "The Coding Sheets")
If mypass = "1234567890" Then
    Sheets("Developer").Visible = True
    Sheets("Developer").Select
    Sheets("Notes").Visible = True
    Application.Visible = True
    Unload Me
End If
End Sub

Note- I've been moving the "Application.Visible" around a bit and the coding is very basic- but it works for me. I've also tried renaming the sheet to see if it would react different, but no avail. As for excel in the background- taskmanager- I believe the program is going in the background- It's not listed in the "Apps" BUT
Windows 7 machine- I can find it in the processes and after the userforms close, it I double click the file to reopen- it opens to one of the sheets and asks if I want to reopen and all changes will be discarded- if I hit yes- repeat process- if I hit No I can get to the sheet.

Windows 10 machine (where I wrote the coding)- I also find in processes but not apps- to access it I let the userforms run and then once the last one closes- I double click and the window opens to a sheet and runs normally....

 

[sassriverrat]

That's why I included the workbook_Open piece- also- I've been trying even after making it to Userform5....

 

[sassriverrat]

anyone got ideas?