Linking userform to userform in Excel 2003
 

Hi All-

I am trying to write a macro that upon opening a spreadsheet, the use
will be asked if s/he would like to create a new template or is usin
an existing template.

If the user is using an existing template, then the promptbox is hidde
- no problems there!

But if the user hits the button to create a new template, I want
second userform to open which will ask the user to input the contrac
name. After the contract name is input, the user will hit an O
button, which will hopefully return the contract name value into cel
A3 of the Executive Summary tab.

So here is what I have done so far:

1. I created a userform named ContractPrompt which contains a text bo
and an ok button. The form asks the user "What is the contract name?"

The code for the text box is:

Private Sub TextBox1_Change()
ActiveWorkbook.Sheets("Executive Summary").Activate
Range("A3").Select
ActiveCell.Value = TextBox1.Value
End Sub

The code for the ok button is:

Private Sub CommandButton1_Click()
ContractPrompt.Hide
End Sub

2. I created a second userform PromptDialog which pops up when th
file is first opened in excel. This form has 2 buttons 'Create a ne
summary' and 'Use existing summary.' As I stated above, if the us
existing summary button is chosen, the prompt hides. But if th
'create new summary' button is chosen, I get a catastrophic failur
message!!!

When the 'create new summary' is chosen, I am trying to tell th
program to show the ContractPrompt userform, so the user can input th
contract name.

Here is the code I have for the 'create new summary' button:

Private Sub CommandButton1_Click()
PromptDialog.Hide
ContractPrompt.Show
End Sub

Here are my questions/problems:
1. Is the code for the 1st userform text correct so that when the use
types in the contract name, it was appear is cell A3 of the Executiv
Summary tab?

2. How do I link the 2 userforms????

Admittedly, I am a beginner in VBA programing and I am sure my dilemma
are rather easy to correct.

Thanks in advance for your help!
-Melissa
:confused

--
Message posted from http://www.ExcelForum.com

Linking userform to userform in Excel 2003
 

That would work, but better would be to use another event. Change fires on
every keystoke.

Private Sub TextBox1_AfterUpdate()
ActiveWorkbook.Sheets("Executive Summary") _
Range("A3").Value = TextBox1.Value
End Sub

Unload the 2nd form rather than hide it:

Private Sub CommandButton1_Click()
unload me
End Sub


close excel and clean out your temp folder and any folders below your temp
folder before you do anything else.

--
Regards,
Tom Ogilvy

"missmelis01 " wrote in message
...
Hi All-

I am trying to write a macro that upon opening a spreadsheet, the user
will be asked if s/he would like to create a new template or is using
an existing template.

If the user is using an existing template, then the promptbox is hidden
- no problems there!

But if the user hits the button to create a new template, I want a
second userform to open which will ask the user to input the contract
name. After the contract name is input, the user will hit an OK
button, which will hopefully return the contract name value into cell
A3 of the Executive Summary tab.

So here is what I have done so far:

1. I created a userform named ContractPrompt which contains a text box
and an ok button. The form asks the user "What is the contract name?"

The code for the text box is:

Private Sub TextBox1_Change()
ActiveWorkbook.Sheets("Executive Summary").Activate
Range("A3").Select
ActiveCell.Value = TextBox1.Value
End Sub

The code for the ok button is:

Private Sub CommandButton1_Click()
ContractPrompt.Hide
End Sub

2. I created a second userform PromptDialog which pops up when the
file is first opened in excel. This form has 2 buttons 'Create a new
summary' and 'Use existing summary.' As I stated above, if the use
existing summary button is chosen, the prompt hides. But if the
'create new summary' button is chosen, I get a catastrophic failure
message!!!

When the 'create new summary' is chosen, I am trying to tell the
program to show the ContractPrompt userform, so the user can input the
contract name.

Here is the code I have for the 'create new summary' button:

Private Sub CommandButton1_Click()
PromptDialog.Hide
ContractPrompt.Show
End Sub

Here are my questions/problems:
1. Is the code for the 1st userform text correct so that when the user
types in the contract name, it was appear is cell A3 of the Executive
Summary tab?

2. How do I link the 2 userforms????

Admittedly, I am a beginner in VBA programing and I am sure my dilemmas
are rather easy to correct.

Thanks in advance for your help!
-Melissa
:confused:


---
Message posted from http://www.ExcelForum.com/

Linking userform to userform in Excel 2003
 

Thanks Tom - with your help I got it to work.
-Meliss

--
Message posted from http://www.ExcelForum.com