Returning control to form
 

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

Returning control to form
 

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)

"P. Dileepan" wrote in message
...
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

Returning control to form
 

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.

--
Regards,
Tom Ogilvy



"P. Dileepan" wrote in message
...
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

Returning control to form
 

-----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.


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

Returning control to form - Followup Q
 

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



"P. Dileepan" wrote in message
...
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



.
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

Returning control to form - Followup Q
 

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





wrote in message
...
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



"P. Dileepan" wrote in message
...
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



.
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

Returning control to form - Followup Q
 

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.

--
Regards,
Tom Ogilvy


wrote in message
...
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



"P. Dileepan" wrote in message
...
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



.
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

Returning control to form - Followup Q
 

-----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
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

Returning control to form - Followup Q
 

Your running your code on a Macintosh computer?

--
Regards,
Tom Ogilvy

"P. Dileepan" wrote in message
...
-----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
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

Returning control to form - Followup Q
 

-----Original Message-----
Your running your code on a Macintosh computer?

No, I am running XP.

-- Dileepan


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




.

Returning control to form - Followup Q
 

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

--
Regards,
Tom Ogilvy



"P. Dileepan" wrote in message
...
-----Original Message-----
Your running your code on a Macintosh computer?

No, I am running XP.

-- Dileepan


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




.

Returning control to form - Followup Q
 

I told you in my original post that you need to have a
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