A lot of easy questions.

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Thanks Bob - I've never used that event before.

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)

 

[Bob Phillips]

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

 

[Guest]

Bob

I always gain more knowledge than I share on this forum!

Regards
Rowan