PDA

View Full Version : Sleeper: Msgbox automatically clicks yes



grohm
05-03-2007, 06:06 AM
Hi guys

Is there a way that i can automatically (via vba) answer message boxes as "yes".

background is the following: i have a excel file, which brings up a couple of messageboxes to approve before you can save/end it. Now, there is no way i can change that. It works fine it is just a little annoying. It would be great to have a "End" button which does close the messageboxes automatically and saves/ends the excel file.
but i dont know if that's even possible?


Greetings,

Ben

johnske
05-03-2007, 06:20 AM
It would be best to look at the code you're using - e.g. are you talking about literal coded message boxes or are you referring to Excels 'Save?' message box - it sounds like you just don't want the message boxes to show at all...

Simon Lloyd
05-03-2007, 06:39 AM
I suspect Johnske that its another person code that is running producing msgbox(s) in the workbook he is using, otherwise changing message boxes would be no problem as he could just delete them!

So i guess he wants to supress message boxes coming from the intial workbook!

Regards,
Simon

grohm
05-04-2007, 05:23 AM
It would be best to look at the code you're using - e.g. are you talking about literal coded message boxes or are you referring to Excels 'Save?' message box - it sounds like you just don't want the message boxes to show at all...


I have a couple of users who want to take some infos out of the file, each user has his own sheet which only he can access (due to a personal password) no the problem is, when i change something and want to save, i have to press "ok" or "cancel" for each and every msgbox which asks for a PW. This is a little bit annoying, since there are some of it. That's why and Button which would automatically save and end the file would be helpful. I pasted one of the personal-code sheets below. They are always the same for each person involved:



Private Sub Worksheet_Activate()
Sheets("user1").Columns("A:AM").Hidden = True
If InputBox("Please enter password....") = "7243" Then
Sheets("user1").Columns("A:AM").Hidden = False
Else
MsgBox ("Sorry, wrong password!")
Sheets("user1").Protect Password:="7243", DrawingObjects:=True, contents:=True, Scenarios:=True, userinterfaceonly:=True
End If
End Sub

Private Sub Worksheet_Deactivate()
Sheets("user1").Unprotect Password:="7243"
End Sub


hope that brings some clarification into it :-)

johnske
05-04-2007, 10:48 PM
Sorry grohm, i fail to see what you want to do here. You have input boxes that require a password in order to access certain portions of the worksheet when it's activated. Naturally, after entering the password the user will then have to click ok or cancel, or hit the enter key.

If you want that sort of worksheet security i fail to see how you could escape that action - without doing away with the password requirement...

johnske
05-05-2007, 06:34 AM
Although this sorta defeats the security purpose a little, it'll do what you want.

You'll need a userform that simulates the appearance of an inputbox (example attached). Change the sheet names and their passwords in the example to suit...

In the Userform Code Module:

Option Explicit

Private Sub UserForm_Activate()
TextBox1.PasswordChar = "*"
End Sub

Private Sub TextBox1_Change()
'
Select Case ActiveSheet.Name
Case "Sheet1"
If TextBox1 = "1234" Then GoSub UnlockSht
Case "Sheet2"
If TextBox1 = "2345" Then GoSub UnlockSht
Case "Sheet3"
If TextBox1 = "3456" Then GoSub UnlockSht
End Select
Exit Sub
UnlockSht:
ActiveSheet.Columns("A:AM").Hidden = False
Unload Me
End Sub

Private Sub CancelButton_Click()
Unload Me
End Sub

In the worksheet code modules:


Option Explicit

Private Sub Worksheet_Activate()
On Error Resume Next
With Me
.Columns("A:AM").Hidden = True
.Protect Password:="1234", _
DrawingObjects:=True, _
contents:=True, _
Scenarios:=True, _
userinterfaceonly:=True
End With
UserForm1.Show False
End Sub

Private Sub Worksheet_Deactivate()
Unload UserForm1
End Sub