Userform question

[chuckles1066]

I'm new to userforms so bear with me.

I have a userform that is loaded from a macro.

Question 1 - instead, can I have the userform autoload whenever the workbook in question is opened and then have something behind the scenes run the macro? I've had forms autoload in Access using a macro named autoexec.

On the userform there are three questions with yes/no answers that need to be ticked accordingly - these are actions that the macro will take (for example, "automatically email the file when finished?" etc).

In the userform initialise section I have the OK button CommandButton1.Enabled set to False to prevent the user clicking OK without having clicked yes/no anywhere.

Question 2 - I need something that is constantly monitoring the userform so that when, and only when, there is a tick in each of the three questions checkboxes, the CommandButton1.Enabled status is toggled to True.

For each of the six checkboxes there is a private sub change routine.

Any help appreciated.

 

[tweedle]

To open things from the get-go, put the call in the Private Sub Workbook_Open() routine.


In the ThisWorkbook code:

Private Sub Workbook_Open()
UserForm1.Show False
Call [I]YourMacroHere[/I]
End Sub

Given a form with 6 checkboxes and 1 command button,
Paste this into the form code;

Dim Q1, Q2, Q3 As Boolean
Private Sub UserForm_Initialize()
'Set the inital values
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
CheckBox5.Value = False
CheckBox6.Value = False
CommandButton1.Enabled = False
End Sub
 
Private Sub CheckBox1_Click()
Q1 = False 'Reset the variable from any previous clicks
If CheckBox1.Value + CheckBox2.Value = -1 Then Q1 = True
EnableGoButton
End Sub
 
Private Sub CheckBox2_Click()
Q1 = False
If CheckBox1.Value + CheckBox2.Value = -1 Then Q1 = True
EnableGoButton
End Sub
 
Private Sub CheckBox3_Click()
Q2 = False
If CheckBox3.Value + CheckBox4.Value = -1 Then Q2 = True
EnableGoButton
End Sub
 
Private Sub CheckBox4_Click()
Q2 = False
If CheckBox3.Value + CheckBox4.Value = -1 Then Q2 = True
EnableGoButton
End Sub
 
Private Sub CheckBox5_Click()
Q3 = False
If CheckBox5.Value + CheckBox6.Value = -1 Then Q3 = True
EnableGoButton
End Sub
 
Private Sub CheckBox6_Click()
Q3 = False
If CheckBox5.Value + CheckBox6.Value = -1 Then Q3 = True
EnableGoButton
End Sub
 
Sub EnableGoButton()
'Checks the status of question set in the checkbox clicks
If Q1 And Q2 And Q3 Then 'if all True
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If
End Sub

 

[chuckles1066]

Many thanks for that, I'll have a play later

 

[chuckles1066]

Ok, everything works as I wanted........except.........

The original template gets discarded without any changes being saved so the macro is good to go again next time.

The emailed file, however, will now fire up the userform when the file is opened by the recipients which is not what I need to happen.

So I need to set a flag of some sort - I was thinking of something along the lines of creating a dummy sheet and hiding it so that the emailed file macro checks to see if it exists and if so doesnt display the form.

Anyone got a better idea?

 

[tweedle]

One could Save it off to another non-macro workbook and e-mail that one...

Here's the basic concept for the saving off...

Dim ShtIdx As Integer
Dim wbThisOne, wbNew As Workbook
Set wbThisOne = Application.ActiveWorkbook
wbThisOne.Worksheets.Copy
Set wbNew = Workbooks(Application.Workbooks.Count)
Application.DisplayAlerts = False
wbNew.SaveAs Replace(wbThisOne.Name, ".xlsm", "") & "_" & _
Replace(Replace(Format(Now(), "yyyy/mm/dd hh:mm:ss"), "/", ""), ":", "") & ".xlsx"
Application.DisplayAlerts = True
wbNew.Close
Set wbNew = Nothing
Set wbThisOne = Nothing

 

[chuckles1066]

Ahhh, apologies - my company are still living in the dark ages and using Office XP so there are no xlsm or xlsx extensions.