Returning control to form

P

P. Dileepan

Hi,

I am not a VBA programmer. My question may be too
simple, but I am stuck. Please help.

In my code I check whether user input is valid or not.
Somethig like this:

If nSeasons = 0 Then ErrMsg1 = Seasons caqnnot be zero!"
If ErrMsg1 <> "" Then MsgBox (ErrMsg1)

How do I make the code execution to put the control back
to the form so that the user can either cancel or give
valid input?

-- Dileepan
 
B

Bob Phillips

Hi Dileepan,

What the user exits the message box, which by the way you can test for
response, then control continues. So your form gets it automatically.

If nSeasons = 0 Then
ans = MsgBox(" Seasons caqnnot be zero!", vbOKCancel)
If ans = vbOK Then
'do one thing
Else
'do another
End If
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

If you mean a textbox on a userform, use the exit event

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
dim nSeasons as Long
if not isnumeric(Textbox1.Text) then
msgbox "Entry must be a number between 1 and 4"
cancel = True
Textbox1.Text = ""
else
nSeasons = clng(Textbox1.Text)
if not (nSeasons >=1 and nSeason <= 4 then
msgbox "Enter must be a number between 1 and 4"
cancel = True
Textbox1.Text = ""
end if
end if
End sub

When you set cancel = true, the textbox is not exited.
 
G

Guest

-----Original Message-----
If you mean a textbox on a userform, use the exit event

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

When you set cancel = true, the textbox is not exited.
Click to expand...


Thank you, this works. But I think I did not explainmy
problem fully. I have several controls in the userform.
Some of them are text boxes and some are RefEdits. I
want to check for validity when the user clicks on OK and
proceed with the computations if all the validty checks
are passed. If not, I want to pass the control back to
the form for the user to enter appropriate input. For
example, in Descriptive Statistics under Data Analysis,
if the user omits some of the required input only when
the user clicks OK the user is alerted and the control is
returned back to the form.

I am sorry to have given incomplete information earlier.
I would very much appreciate a response.

Also, I want to use "Regression" command of Excel in my
code to generate coeefficients. If I have only one Y and
one X I can use the Intercept and Slope functions. But I
have several X's that must be regressed on Y. How can I
use the Excel Regression command inside VBA code?

Thanks again,

-- Dileepan
 
G

Guest

Sorry, I forgot to put my e-mail ID when I posted
this followup question.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

When you set cancel = true, the textbox is not exited.

--
Regards,
Tom Ogilvy






.
Thank you, this works. But I think I did not explainmy
Click to expand...
problem fully. I have several controls in the userform.
Some of them are text boxes and some are RefEdits. I
want to check for validity when the user clicks on OK and
proceed with the computations if all the validty checks
are passed. If not, I want to pass the control back to
the form for the user to enter appropriate input. For
example, in Descriptive Statistics under Data Analysis,
if the user omits some of the required input only when
the user clicks OK the user is alerted and the control is
returned back to the form.

I am sorry to have given incomplete information earlier.
I would very much appreciate a response.

Also, I want to use "Regression" command of Excel in my
code to generate coeefficients. If I have only one Y and
one X I can use the Intercept and Slope functions. But I
have several X's that must be regressed on Y. How can I
use the Excel Regression command inside VBA code?

Thanks again,

-- Dileepan
 
T

Tom Ogilvy

Private Sub CommandButton1_Click()
Dim tbox As MSForms.TextBox
Dim rEdit As RefEdit.RefEdit
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is RefEdit.RefEdit Then
Set rEdit = ctrl
If ctrl.Text = "" Then
MsgBox "Bad Data in " & rEdit.Name
rEdit.SetFocus
Set rEdit = Nothing
Exit Sub
End If
ElseIf TypeOf ctrl Is MSForms.TextBox Then
Set tbox = ctrl
If ctrl.Text = "" Then
MsgBox "Bad Data in " & tbox.Name
tbox.SetFocus
Set tbox = Nothing
Exit Sub
End If
End If
Next
Unload me
End Sub


Might get you started.

--
regards,
Tom Ogilvy
 
T

Tom Ogilvy

Regression is a function in the analysis toolpak. Turn on the macro
recorder and do a regression analysis. This will show you how to use it.

Make sure you have Analysis Tookpak - VBA checked under tools=>Addins.
 
P

P. Dileepan

-----Original Message-----
Regression is a function in the analysis toolpak. Turn on the macro
recorder and do a regression analysis. This will show you how to use it.

Make sure you have Analysis Tookpak - VBA checked under
Click to expand...
tools=>Addins.

I did that, but I am getting an error:

Here is the code:
=====
AddIns("Analysis ToolPak").Installed = True
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range
("$C$7:$C$30"), _
ActiveSheet.Range("$D$8:$D$31"), False, False, ,
ActiveSheet.Range("$M$1") _
, False, False, False, False, , False
====

The error I am getting is "ATPVBAEN.XLA could not be
found". There is a knowledgebse article about this at
the MS site. I copied the work around they have
suggested, namely,

====From Microsoft Knowledge Base Article - 192642 ===
you would change Atpvbaen.xla to 'Analysis Toolpak - VBA'
as follows: Application.Run "'Analysis Toolpak - VBA'!
Histogram", _
ActiveSheet.Range("$A$1:$A$4"), ActiveSheet.Range
("$E$1"), _
ActiveSheet.Range("$B$1:$B$3"), False, False, False,
False)
========================

Still I get the same error, "'Analysis Toolpak - VBA.xls'
could not be found".

It adds the .xls to 'Analysis Toolpak - VBA' and says it
cannot be found. I copied and pasted this code from the
MS knlowedgebase article.

Can you please help!

Thank you and besty regards, Dileepan
 
T

Tom Ogilvy

The article is only for a mac. It has no relevance to windows.

I told you in my original post that you need to have a check mark next to
"Analysis Toolpak -VBA" in tools=>Addins. This causes the ATPVBAEN.XLA file
to be loaded.

Then this code will run:

Application.Run "ATPVBAEN.XLA!Regress", _
ActiveSheet.Range("$C$7:$C$30"), _
ActiveSheet.Range("$D$8:$D$31"), False, False, , _
ActiveSheet.Range("$M$1") _
, False, False, False, False, , False

Analysis Toolpak provides the functions for Excel
Analysis Toolpak - VBA provides the same functions for use in VBA
 
P

P. Dileepan

I told you in my original post that you need to have a
Click to expand...
check mark next to "Analysis Toolpak -VBA"

You are right, it works. I am sorry I overlooked the VBA
part. I did have Analysis Toolpack checked, but not
Analysis Toolpack - VBA. It works fine now. I thank you
very much and appreciate your patience with me.

I do have another problem, but I will post that in a
separate subject line.

-- Dileepan
 
Top