Linking userform to userform in Excel 2003

[missmelis01]

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

 

[Tom Ogilvy]

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.

 

[missmelis01]

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