code problem not working HELP
in [Programming]
|
From: BillD on 1 Nov 2009 19:27 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 -- BillD
From: JLGWhiz on 1 Nov 2009 21:11 See if this site helps any: http://puremis.net/excel/code/063.shtml "BillD" <BillD(a)discussions.microsoft.com> wrote in message news:0CCDEF11-1BCC-41D9-AAB1-A93A3482C7A0(a)microsoft.com... >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 > -- > BillD
From: OssieMac on 1 Nov 2009 22:51 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. -- Regards, OssieMac
From: BillD on 2 Nov 2009 12:06 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 -- BillD "OssieMac" wrote: > 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. > > -- > Regards, > > OssieMac > >
From: OssieMac on 2 Nov 2009 19:09 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" -- Regards, OssieMac
|
Pages: 1 Prev: Item Count vs Item Cost Next: Upload Excel Workbook On SharePoint |