Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A lot of easy questions.
Hi, Im making a stock program and the following problems have happened.
1) When I make a user form appears a red square with a cross in it (a close form button I suppose), the problem is that I have a form that ask for a password, so when the user clicks on the cross, all the macro assigned to the form is jumped and the macro that should be executed when the password is typed, is run ¿how do I solve the cross thing (some way to disable or not to show it), 2) Is there a function that hides all the active tool bars?? 3) I have a macro that automatically print a document in a specific printer, I want that the user specifies the printer (I mean, a macro that shows the print form) is there a function that allows this??? 4) Check your excel program, in the top in your right hand there are 3 buttons (close, maximize and minimize) there is some way to keep it away (or disable them)???? 5) There is some way to change the excels pre-instaled forms and/or to change excels codes (I mean the ones that microsoft mades) TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A lot of easy questions.
1) When a macro shows a form it stops until the form is closed and then
continues from there. Consider: Sub Start() Userform1.Show Password sensitive code end Sub If your macro is like this then the password sensitive code will always run no matter how the form is closed. This is because when the form is closed the macro continues to run to the end. Now consider: Sub Start() Userform1.Show End Sub Private Sub CancelButton_Click() Unload Me End Sub Private Sub OKButton_Click() check password if password good then Password sensitive code here end if Unload Me End Sub In this situation the password sensitive code will only be run after the OK button is clicked and the password is verified. If the form is closed using the X the password sensitive code will never be run. 2) If you really want to hide all toolbars then there are a few things to consider. If you hide the toolbars and the user is also in another spreadsheet then they will not have access to the toolbars in that spreadsheet either. If you do not show the toolbars again before exiting Excel they will not be shown the next time the user opens Excel. So, with this in mind, you can use workbook_activate and deactivate events to hide and show the toolbars. Your macro to hide the toolbars will have to make a list of the toolbars it hides so that the deactivate event knows which toolbars to show. The code in the ThisWorkbook module should look something like this and relies on a hidden sheet called TB: Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop End Sub 3) try: Application.Dialogs(xlDialogPrint).Show 4) Not sure on that one 5) Not sure what you mean here. You can for example change the Caption on a built in dialog when calling it. I very much doubt that you can change the actual functionality of one of these forms though. Regards Rowan "filo666" wrote: Hi, Im making a stock program and the following problems have happened. 1) When I make a user form appears a red square with a cross in it (a close form button I suppose), the problem is that I have a form that ask for a password, so when the user clicks on the cross, all the macro assigned to the form is jumped and the macro that should be executed when the password is typed, is run ¿how do I solve the cross thing (some way to disable or not to show it), 2) Is there a function that hides all the active tool bars?? 3) I have a macro that automatically print a document in a specific printer, I want that the user specifies the printer (I mean, a macro that shows the print form) is there a function that allows this??? 4) Check your excel program, in the top in your right hand there are 3 buttons (close, maximize and minimize) there is some way to keep it away (or disable them)???? 5) There is some way to change the excels pre-instaled forms and/or to change excels codes (I mean the ones that microsoft mades) TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A lot of easy questions.
1) Take a look at the Queryclose event in help, it allows you to control the
form exit. -- HTH RP (remove nothere from the email address if mailing direct) "Rowan" wrote in message ... 1) When a macro shows a form it stops until the form is closed and then continues from there. Consider: Sub Start() Userform1.Show Password sensitive code end Sub If your macro is like this then the password sensitive code will always run no matter how the form is closed. This is because when the form is closed the macro continues to run to the end. Now consider: Sub Start() Userform1.Show End Sub Private Sub CancelButton_Click() Unload Me End Sub Private Sub OKButton_Click() check password if password good then Password sensitive code here end if Unload Me End Sub In this situation the password sensitive code will only be run after the OK button is clicked and the password is verified. If the form is closed using the X the password sensitive code will never be run. 2) If you really want to hide all toolbars then there are a few things to consider. If you hide the toolbars and the user is also in another spreadsheet then they will not have access to the toolbars in that spreadsheet either. If you do not show the toolbars again before exiting Excel they will not be shown the next time the user opens Excel. So, with this in mind, you can use workbook_activate and deactivate events to hide and show the toolbars. Your macro to hide the toolbars will have to make a list of the toolbars it hides so that the deactivate event knows which toolbars to show. The code in the ThisWorkbook module should look something like this and relies on a hidden sheet called TB: Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop End Sub 3) try: Application.Dialogs(xlDialogPrint).Show 4) Not sure on that one 5) Not sure what you mean here. You can for example change the Caption on a built in dialog when calling it. I very much doubt that you can change the actual functionality of one of these forms though. Regards Rowan "filo666" wrote: Hi, I'm making a stock program and the following problems have happened. 1) When I make a user form appears a red square with a cross in it (a close form button I suppose), the problem is that I have a form that ask for a password, so when the user clicks on the cross, all the macro assigned to the form is jumped and the macro that should be executed when the password is typed, is run ¿how do I solve the cross thing (some way to disable or not to show it), 2) Is there a function that hides all the active tool bars?? 3) I have a macro that automatically print a document in a specific printer, I want that the user specifies the printer (I mean, a macro that shows the print form) is there a function that allows this??? 4) Check your excel program, in the top in your right hand there are 3 buttons (close, maximize and minimize) there is some way to keep it away (or disable them)???? 5) There is some way to change the excels pre-instaled forms and/or to change excels codes (I mean the ones that microsoft mades) TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A lot of easy questions.
Thanks Bob - I've never used that event before.
"Bob Phillips" wrote: 1) Take a look at the Queryclose event in help, it allows you to control the form exit. -- HTH RP (remove nothere from the email address if mailing direct) "Rowan" wrote in message ... 1) When a macro shows a form it stops until the form is closed and then continues from there. Consider: Sub Start() Userform1.Show Password sensitive code end Sub If your macro is like this then the password sensitive code will always run no matter how the form is closed. This is because when the form is closed the macro continues to run to the end. Now consider: Sub Start() Userform1.Show End Sub Private Sub CancelButton_Click() Unload Me End Sub Private Sub OKButton_Click() check password if password good then Password sensitive code here end if Unload Me End Sub In this situation the password sensitive code will only be run after the OK button is clicked and the password is verified. If the form is closed using the X the password sensitive code will never be run. 2) If you really want to hide all toolbars then there are a few things to consider. If you hide the toolbars and the user is also in another spreadsheet then they will not have access to the toolbars in that spreadsheet either. If you do not show the toolbars again before exiting Excel they will not be shown the next time the user opens Excel. So, with this in mind, you can use workbook_activate and deactivate events to hide and show the toolbars. Your macro to hide the toolbars will have to make a list of the toolbars it hides so that the deactivate event knows which toolbars to show. The code in the ThisWorkbook module should look something like this and relies on a hidden sheet called TB: Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop End Sub 3) try: Application.Dialogs(xlDialogPrint).Show 4) Not sure on that one 5) Not sure what you mean here. You can for example change the Caption on a built in dialog when calling it. I very much doubt that you can change the actual functionality of one of these forms though. Regards Rowan "filo666" wrote: Hi, I'm making a stock program and the following problems have happened. 1) When I make a user form appears a red square with a cross in it (a close form button I suppose), the problem is that I have a form that ask for a password, so when the user clicks on the cross, all the macro assigned to the form is jumped and the macro that should be executed when the password is typed, is run ¿how do I solve the cross thing (some way to disable or not to show it), 2) Is there a function that hides all the active tool bars?? 3) I have a macro that automatically print a document in a specific printer, I want that the user specifies the printer (I mean, a macro that shows the print form) is there a function that allows this??? 4) Check your excel program, in the top in your right hand there are 3 buttons (close, maximize and minimize) there is some way to keep it away (or disable them)???? 5) There is some way to change the excels pre-instaled forms and/or to change excels codes (I mean the ones that microsoft mades) TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A lot of easy questions.
Hi Rowan,
Indispensable when working with forms and those damn user <.g. It wasn't aimed at you, but if you get something from it, even better :-) Regards Bob "Rowan" wrote in message ... Thanks Bob - I've never used that event before. "Bob Phillips" wrote: 1) Take a look at the Queryclose event in help, it allows you to control the form exit. -- HTH RP (remove nothere from the email address if mailing direct) "Rowan" wrote in message ... 1) When a macro shows a form it stops until the form is closed and then continues from there. Consider: Sub Start() Userform1.Show Password sensitive code end Sub If your macro is like this then the password sensitive code will always run no matter how the form is closed. This is because when the form is closed the macro continues to run to the end. Now consider: Sub Start() Userform1.Show End Sub Private Sub CancelButton_Click() Unload Me End Sub Private Sub OKButton_Click() check password if password good then Password sensitive code here end if Unload Me End Sub In this situation the password sensitive code will only be run after the OK button is clicked and the password is verified. If the form is closed using the X the password sensitive code will never be run. 2) If you really want to hide all toolbars then there are a few things to consider. If you hide the toolbars and the user is also in another spreadsheet then they will not have access to the toolbars in that spreadsheet either. If you do not show the toolbars again before exiting Excel they will not be shown the next time the user opens Excel. So, with this in mind, you can use workbook_activate and deactivate events to hide and show the toolbars. Your macro to hide the toolbars will have to make a list of the toolbars it hides so that the deactivate event knows which toolbars to show. The code in the ThisWorkbook module should look something like this and relies on a hidden sheet called TB: Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop End Sub 3) try: Application.Dialogs(xlDialogPrint).Show 4) Not sure on that one 5) Not sure what you mean here. You can for example change the Caption on a built in dialog when calling it. I very much doubt that you can change the actual functionality of one of these forms though. Regards Rowan "filo666" wrote: Hi, I'm making a stock program and the following problems have happened. 1) When I make a user form appears a red square with a cross in it (a close form button I suppose), the problem is that I have a form that ask for a password, so when the user clicks on the cross, all the macro assigned to the form is jumped and the macro that should be executed when the password is typed, is run ¿how do I solve the cross thing (some way to disable or not to show it), 2) Is there a function that hides all the active tool bars?? 3) I have a macro that automatically print a document in a specific printer, I want that the user specifies the printer (I mean, a macro that shows the print form) is there a function that allows this??? 4) Check your excel program, in the top in your right hand there are 3 buttons (close, maximize and minimize) there is some way to keep it away (or disable them)???? 5) There is some way to change the excels pre-instaled forms and/or to change excels codes (I mean the ones that microsoft mades) TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A lot of easy questions.
Bob
I always gain more knowledge than I share on this forum! Regards Rowan "Bob Phillips" wrote: Hi Rowan, Indispensable when working with forms and those damn user <.g. It wasn't aimed at you, but if you get something from it, even better :-) Regards Bob "Rowan" wrote in message ... Thanks Bob - I've never used that event before. "Bob Phillips" wrote: 1) Take a look at the Queryclose event in help, it allows you to control the form exit. -- HTH RP (remove nothere from the email address if mailing direct) "Rowan" wrote in message ... 1) When a macro shows a form it stops until the form is closed and then continues from there. Consider: Sub Start() Userform1.Show Password sensitive code end Sub If your macro is like this then the password sensitive code will always run no matter how the form is closed. This is because when the form is closed the macro continues to run to the end. Now consider: Sub Start() Userform1.Show End Sub Private Sub CancelButton_Click() Unload Me End Sub Private Sub OKButton_Click() check password if password good then Password sensitive code here end if Unload Me End Sub In this situation the password sensitive code will only be run after the OK button is clicked and the password is verified. If the form is closed using the X the password sensitive code will never be run. 2) If you really want to hide all toolbars then there are a few things to consider. If you hide the toolbars and the user is also in another spreadsheet then they will not have access to the toolbars in that spreadsheet either. If you do not show the toolbars again before exiting Excel they will not be shown the next time the user opens Excel. So, with this in mind, you can use workbook_activate and deactivate events to hide and show the toolbars. Your macro to hide the toolbars will have to make a list of the toolbars it hides so that the deactivate event knows which toolbars to show. The code in the ThisWorkbook module should look something like this and relies on a hidden sheet called TB: Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop End Sub 3) try: Application.Dialogs(xlDialogPrint).Show 4) Not sure on that one 5) Not sure what you mean here. You can for example change the Caption on a built in dialog when calling it. I very much doubt that you can change the actual functionality of one of these forms though. Regards Rowan "filo666" wrote: Hi, I'm making a stock program and the following problems have happened. 1) When I make a user form appears a red square with a cross in it (a close form button I suppose), the problem is that I have a form that ask for a password, so when the user clicks on the cross, all the macro assigned to the form is jumped and the macro that should be executed when the password is typed, is run ¿how do I solve the cross thing (some way to disable or not to show it), 2) Is there a function that hides all the active tool bars?? 3) I have a macro that automatically print a document in a specific printer, I want that the user specifies the printer (I mean, a macro that shows the print form) is there a function that allows this??? 4) Check your excel program, in the top in your right hand there are 3 buttons (close, maximize and minimize) there is some way to keep it away (or disable them)???? 5) There is some way to change the excels pre-instaled forms and/or to change excels codes (I mean the ones that microsoft mades) TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Couple of hopefully easy questions | Excel Worksheet Functions | |||
two easy questions take two | Excel Programming | |||
Two Easy questions | Excel Programming | |||
Two really easy questions | Excel Programming |