| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check UserForm Values Before Saving
Hi,
Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
What happens when that sub is exited, does it still unload?
-- HTH Bob Phillips "MBlake" wrote in message ... Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
You have to add an exit sub in each part of your if . . . elseif construct.
-- Regards, Tom Ogilvy "MBlake" wrote in message ... Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
What seems to be happening is that if there is a value missing, the
userform is giving focus to the correct control, but then following through with the code to copy the values to the database worksheet. You might try adding the "Exit Sub" to all your ElseIf statements, that should bypass all the other code in the proc, and still show the userform. Also, is your userform hidden, ie Userform.Hide, before or after this code. If it is before, then you need to show it again. Or you could use a GoTo statement to do the same thing. *** Sent via Developersdex http://www.developersdex.com *** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Excellent help, many thanks for the replies. I have amended the code to
include an Exit Sub after each check, all works fine now. Regards, Mickey ============================================= If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus Exit Sub ElseIf ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus Exit Sub etc .......... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Mickey,
My suggestion will be to use a routine such as follows behind the 'Submit' button, which I have here assumed is called butSubmit. Sub butSubmit_Click() Dim sMsg$ If ComboBoxPIN.Text = "" Then sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf End If If Me.ComboBoxDate.Value = "" Then sMsg = sMsg & "You Must Enter a Date." & vbCrLf End If < similar other checks If sMsg < "" Then MsgBox sMsg Exit Sub End If <following code then copies the userform values to the database worksheet 'This will unload the form Unload Me End Sub Here the advantage is that a dialog box points out all the problems with the data together. If the user does not fix all the problems, the submit button does not exit from the form and does not save to the database sheet. Alok Joshi "MBlake" wrote: Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Alok,
I will give your code a go, it looks neater. Thanks, Mickey |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Alok,
Your code works well and produces one MsgBox, however is there any way to then SetFocus to the first missing entry?. In the code I was using the SetFocus shelps the user. I guess I could default the SetFocu by adding ComboBoxPIN.SetFocus as in If sMsg < "" Then MsgBox sMsg Exit Sub ComboBoxPIN.SetFocus End If Best Wishes, Mickey "Alok" wrote in message ... Hi Mickey, My suggestion will be to use a routine such as follows behind the 'Submit' button, which I have here assumed is called butSubmit. Sub butSubmit_Click() Dim sMsg$ If ComboBoxPIN.Text = "" Then sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf End If If Me.ComboBoxDate.Value = "" Then sMsg = sMsg & "You Must Enter a Date." & vbCrLf End If < similar other checks If sMsg < "" Then MsgBox sMsg Exit Sub End If <following code then copies the userform values to the database worksheet 'This will unload the form Unload Me End Sub Here the advantage is that a dialog box points out all the problems with the data together. If the user does not fix all the problems, the submit button does not exit from the form and does not save to the database sheet. Alok Joshi "MBlake" wrote: Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Mickey,
How about setting focus within each if statement like this "MBlake" wrote: Hi Alok, Your code works well and produces one MsgBox, however is there any way to then SetFocus to the first missing entry?. In the code I was using the SetFocus shelps the user. I guess I could default the SetFocu by adding ComboBoxPIN.SetFocus as in If sMsg < "" Then MsgBox sMsg Exit Sub ComboBoxPIN.SetFocus End If Best Wishes, Mickey "Alok" wrote in message ... Hi Mickey, My suggestion will be to use a routine such as follows behind the 'Submit' button, which I have here assumed is called butSubmit. Sub butSubmit_Click() Dim sMsg$ If ComboBoxPIN.Text = "" Then sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf End If If Me.ComboBoxDate.Value = "" Then sMsg = sMsg & "You Must Enter a Date." & vbCrLf End If < similar other checks If sMsg < "" Then MsgBox sMsg Exit Sub End If <following code then copies the userform values to the database worksheet 'This will unload the form Unload Me End Sub Here the advantage is that a dialog box points out all the problems with the data together. If the user does not fix all the problems, the submit button does not exit from the form and does not save to the database sheet. Alok Joshi "MBlake" wrote: Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Mickey,
Sorry for the earlier post - it was incomplete - I mistakenly hit a return. You can use the following line within each if statement but before the line which start with sMsg = sMsg & "...." if sMsg<"" then xxxx.setFocus This will ensure focus moves to the first problem control. Alok Joshi "MBlake" wrote: Hi Alok, Your code works well and produces one MsgBox, however is there any way to then SetFocus to the first missing entry?. In the code I was using the SetFocus shelps the user. I guess I could default the SetFocu by adding ComboBoxPIN.SetFocus as in If sMsg < "" Then MsgBox sMsg Exit Sub ComboBoxPIN.SetFocus End If Best Wishes, Mickey "Alok" wrote in message ... Hi Mickey, My suggestion will be to use a routine such as follows behind the 'Submit' button, which I have here assumed is called butSubmit. Sub butSubmit_Click() Dim sMsg$ If ComboBoxPIN.Text = "" Then sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf End If If Me.ComboBoxDate.Value = "" Then sMsg = sMsg & "You Must Enter a Date." & vbCrLf End If < similar other checks If sMsg < "" Then MsgBox sMsg Exit Sub End If <following code then copies the userform values to the database worksheet 'This will unload the form Unload Me End Sub Here the advantage is that a dialog box points out all the problems with the data together. If the user does not fix all the problems, the submit button does not exit from the form and does not save to the database sheet. Alok Joshi "MBlake" wrote: Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Mickey,
Sorry for the previos post. I sent it incomplete by mistake. Change each of the If statements like shown below If ComboBoxPIN.Text = "" Then if sMsg="" then ComboBoxPIN.SetFocus sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf End If This will make the focus go the first problem control. Alok Joshi "Alok" wrote: Hi Mickey, How about setting focus within each if statement like this "MBlake" wrote: Hi Alok, Your code works well and produces one MsgBox, however is there any way to then SetFocus to the first missing entry?. In the code I was using the SetFocus shelps the user. I guess I could default the SetFocu by adding ComboBoxPIN.SetFocus as in If sMsg < "" Then MsgBox sMsg Exit Sub ComboBoxPIN.SetFocus End If Best Wishes, Mickey "Alok" wrote in message ... Hi Mickey, My suggestion will be to use a routine such as follows behind the 'Submit' button, which I have here assumed is called butSubmit. Sub butSubmit_Click() Dim sMsg$ If ComboBoxPIN.Text = "" Then sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf End If If Me.ComboBoxDate.Value = "" Then sMsg = sMsg & "You Must Enter a Date." & vbCrLf End If < similar other checks If sMsg < "" Then MsgBox sMsg Exit Sub End If <following code then copies the userform values to the database worksheet 'This will unload the form Unload Me End Sub Here the advantage is that a dialog box points out all the problems with the data together. If the user does not fix all the problems, the submit button does not exit from the form and does not save to the database sheet. Alok Joshi "MBlake" wrote: Hi, Prio to saving UserForm values to a worksheet I want to verify that a user has completed all sections on the form. I have written the following code that traps the missing entries and displays a dialog box. Unfortunately the dialog box then closes and rather than SetFocus the data is transferred to the database worksheet. How can I stop the code to force the user to complete the missing entry and click the 'Submit' buttton? Thanks for any help, Mickey ================================================== ======================== If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number." ComboBoxPIN.SetFocus ElseIf Me.ComboBoxDate.Value = "" Then MsgBox "You Must Enter a Date." ComboBoxDate.SetFocus ElseIf ComboBoxArea.Text = "" Then MsgBox "You Must Enter a Location for this activity." ComboBoxArea.SetFocus ElseIf ComboBoxActivity.Text = "" Then MsgBox "You Must Enter an Activity." ComboBoxActivity.SetFocus ElseIf ComboBoxOffence.Text = "" Then MsgBox "You Must Enter an Offence." ComboBoxOffence.SetFocus ElseIf TextBox11.Text = "" Then MsgBox "You Must Enter a Reference Number." TextBox11.SetFocus Exit Sub End If <following code then copies the userform values to the database worksheet |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Alok,
Thanks for your help which has been invaluable. One final query on this point, I need to check that a TextBox does not have 0 entered. I have tried running the line twice using different parameters but that fails. Do you know if I can do this? I have tried ("" Or 0) as a Value and also the below. Thanks, Mickey If Me.TextBox10.Value <= 1 Then If sMsg = "" Then TextBox10.SetFocus sMsg = sMsg & "You Must Enter the Number of Offenders relating to this entry." & vbCrLf End If If Me.TextBox10.Value "" Then If sMsg = "" Then TextBox10.SetFocus sMsg = sMsg & "You Must Enter the Number of Offenders relating to this entry." & vbCrLf End If |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Mickey,
The number in the text box has to be greater than 1? if so try the condition if val(textbox10.text)<=1 then this will also take care of non-numeric information - as Val will convert "a" or "b" to zero. Alok "MBlake" wrote: Hi Alok, Thanks for your help which has been invaluable. One final query on this point, I need to check that a TextBox does not have 0 entered. I have tried running the line twice using different parameters but that fails. Do you know if I can do this? I have tried ("" Or 0) as a Value and also the below. Thanks, Mickey If Me.TextBox10.Value <= 1 Then If sMsg = "" Then TextBox10.SetFocus sMsg = sMsg & "You Must Enter the Number of Offenders relating to this entry." & vbCrLf End If If Me.TextBox10.Value "" Then If sMsg = "" Then TextBox10.SetFocus sMsg = sMsg & "You Must Enter the Number of Offenders relating to this entry." & vbCrLf End If |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check UserForm Values Before Saving
Hi Alok,
That worked a treat, I am very grateful to you for your help. Thanks, Mickey |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving Userform as Gif | Excel Programming | |||
| Automaticly saving formula's to values when saving | Excel Discussion (Misc queries) | |||
| UserForm value check | Excel Programming | |||
| Spell Check Within a UserForm? | Excel Programming | |||
| Can't check checkboxes in userform ... sometimes | Excel Programming | |||
![MBlake[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
