Sleeper: Msgbox automatically clicks yes

[grohm]

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]

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]

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]

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]

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]

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