code problem not working HELP

[BillD]

I am still learning this code stuff so any and all help will be appreciated
with some detail on the code problem I am having. There are 2 problems:

1- I am trying to create a userform where only the form shows when I open
the application. The code I have below opens the application and then opens
the form but the application is still visible, and I cannot get into the
application to make changes or view reports/charts. I don't know if I have
something out of order or missing please advise on the correct code:

In This Workbook I entered the following:

Private Sub Workbook_Open()
Load userform1
userform1.show
Application.visible=False

2. In order to get the workbook visible so I can make changes and view
charts I am going to put a command button on the userform, but I want the
button to be passworded. I want to click on the button and make the
application visible and allow for changes, viewing and printing
charts/reports.

What and where do I add code to view the workbook, and if possible to make
the workbook not visible after I am done with changes? Also I need to know
what to put in place of Sheets("sheet2").select so that the workbook opens
back up
Here is the code for the command button

Private Sub Commandbutton1_Click()
dim PWD as string
PWD = inputbox(prompt:="Enter Password")
if PWD = "xxxxxxxxx" then
Sheets("Sheet2").select
else
msgbox "incorrect password"
End if
End sub

 

[JLGWhiz]

See if this site helps any:

http://puremis.net/excel/code/063.shtml

 

[OssieMac]

Hello Bill,

Try the following.

Userform property. (Right click anywhere on a blank part of the UserForm)
Set the property ShowModal to False. (this is required so that you can work
on the worksheets while the userform is showing.)

Button on userform to unhide the application:
Set Caption to "Unhide Application". When you click Unhide Application, the
caption on this button will change to "Hide Application" and you can use it
again later to hide the application.

Create a text box on the userform for the password and set PasswordChar
property to * and then set the Visible property to False.
Create a label for the text box and set Caption property to Enter Password
and then set Visible property to False.
Using a textbox for the password allows the password to show as asterisks as
per normal password entry.

Right click on a blank part of the userform and select View Code and then
copy the following code (between the asterisk lines) into the Userform’s code
area. You may need to edit the CommandButton Name, Textbox Name and Label
Name.

Note the comments in Private Sub UserForm_Terminate().

'*******************************************************

Private Sub CommandButton1_Click()
Application.EnableEvents = False
Me.Label1.Visible = True
Me.TextBox1.Value = ""
Me.TextBox1.Visible = True
Me.TextBox1.SetFocus
Application.EnableEvents = True
End Sub

Private Sub TextBox1_AfterUpdate()
Dim strCaption As String
strCaption = Me.CommandButton1.Caption
Application.EnableEvents = False
If Me.TextBox1.Visible = True Then
If Me.TextBox1.Value = "mypassword" Then
Select Case Me.CommandButton1.Caption
Case "Unhide Application"
Application.Visible = True
Me.CommandButton1.Caption = "Hide Application"
Case "Hide Application"
Application.Visible = False
Me.CommandButton1.Caption = "Unhide Application"
End Select
Me.TextBox1.Value = ""
Else
MsgBox "Incorrect password." & vbCrLf & _
"Click " & strCaption & " again"
Me.TextBox1.Value = ""
End If
Me.Label1.Visible = False
Me.TextBox1.Visible = False
End If
Application.EnableEvents = True
End Sub

Private Sub UserForm_Terminate()
'This sub required so that if the user closes the
'Userform while the application is not visible then
'the Application gets closed otherwise an Excel
'Process remains open in the background. You can see
'this in TaskManager (Ctrl/Alt/Del to open Task Manager)

If Application.Visible = False Then
Application.Quit
End If
End Sub

'******************************************************

Replace your Workbook Open event with the following

Private Sub Workbook_Open()
Load UserForm1
UserForm1.Show
UserForm1.CommandButton1.Caption = "Unhide Application"
Application.Visible = False
End Sub


Feel free to get back to me if you have any problems.

 

[BillD]

This code worked great I can't thank you enough for your help and making it
easy to understand. I am putting this in my notebook for future use and
reference.
After running this and seeing what it does I have a few questions though. If
this would be to complex to do I will understnad and you can disregard them.

1- After I click on unhide and enter the password the application opens up
but the userform is above the application and gets in the way of working on
and viewing items like charts, and I cant bring the application forward.
Is it possible to add a minimize button to the title bar of the userform so
it is out of the way and then I can maximize it again to use the hide
appliciation - That or some code to minimize the userform and then maximize
it when I am done with the application?

2- Is it possible to diable the "X" close button on the title bar of the
userform? (I have a close button on the form that will do this).

3- This question can be disregarded if it would entail complex code over my
head. When the command button is at "Hide Application" can I set it so I
don't have to enter the password when I click on the button? (Just click the
button and the application Hides. If not I can live with the password thing).

One last thing as I am at the geginner level learning this VBA code could
you recommend a good beginners level book. All of the books I have seen so
far seem to be for intermediate or advanced levels of experience.

Thanks again
Bill

 

[OssieMac]

Hi Bill,

You could replace all of your code with the following (between the asterisk
lines) for the userform. It will hide the userform when the application is
made visible. Also password only required to unhide the application; no
password required to hide the application. See at bottom of post to unhide
the userform again before hiding the application.

Note the comments in Private Sub CommandButton2_Click. Whatever other code
you have with this button, you must include Unload Me to force the
UserForm_Terminate event to run. Do not just hide the userform otherwise you
will have a process open in the background because Excel has not closed
properly. If a process is left open then you have to close it in Task Manager
otherwise the file will only open in Read Only mode.

'**********************************************************
Private Sub CommandButton2_Click()
'Button to close the form.
'Must contain Unload Me
'Otherwise UserForm_Terminate will not be called
'and the application process will remain open.

Unload Me

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
'Cancels the close action initiated by clicking the red cross.
If CloseMode = vbFormControlMenu Then
Cancel = True
End If

End Sub

Private Sub CommandButton1_Click()
Select Case Me.CommandButton1.Caption
Case "Unhide Application"
Me.Label1.Visible = True
Me.TextBox1.Value = ""
Me.TextBox1.Visible = True
Me.TextBox1.SetFocus
Case "Hide Application"
Me.Label1.Visible = False
Me.TextBox1.Value = ""
Me.TextBox1.Visible = False
Application.Visible = False
Me.CommandButton1.Caption = "Unhide Application"
End Select

End Sub


Private Sub TextBox1_AfterUpdate()
Dim strCaption As String
strCaption = Me.CommandButton1.Caption
If Me.TextBox1.Visible = True Then
If Me.TextBox1.Value = "mypassword" Then
Application.Visible = True
Me.CommandButton1.Caption = "Hide Application"
Me.TextBox1.Value = ""
Me.Hide
Else
MsgBox "Incorrect password." & vbCrLf & _
"Click " & strCaption & " again"
Me.TextBox1.Value = ""
End If
Me.Label1.Visible = False
Me.TextBox1.Visible = False
End If
End Sub

Private Sub UserForm_Terminate()
'This sub required so that if the user closes the
'Userform while the application is not visible then
'the Application gets closed otherwise an Excel
'Process remains open in the background. You can see
'this in TaskManager (Ctrl/Alt/Del to open Task Manager)

If Application.Visible = False Then
Application.Quit
End If
End Sub

'**********************************************************

Now to unhide the userform after editing the worksheets, create a command
button on one of your worksheets and insert the following code into the
button click event.

UserForm1.Show
UserForm1.CommandButton1.Caption = "Hide Application"