Custom form on opening - I think it's an easy one for the experts

[David]

Hi,

I know excel and macros, but unfortunately I'm not so
good with VBA. Can anyone tell me the code to do the
following:

I would like to create a custom form that asks: "Please
enter firm name" The user would then write in the name
i.e. firm A and press ok. What ever is written in that
text box would be populated in field c4 and they could
fill in the rest of the worksheet.

I can make the control and form just fine, but I don't
know how to do the code to make that form pop up on start
up, populate field c4 and then go away once the user
enters the info and press ok.

Also I will be emailing it to several different parties.
Is there away to make the code go into the workseet and
not in something like my personal.xls where I put my
macros?

Your help would be greatly appreciated.

Thanks,
David

 

[Bob Phillips]

To make the form popup

Userform1.Show

The code to populate C4 on clicking OK is

Private Sub cmdOK_Click()
Worksheets("Sheet1").Range("C4").Value = Textbox1.Text
End Sub


--

HTH

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

 

[Dave]

thanks for the quick reply. Can you give the whole code
from start to finish on how that would go in?

Thanks,
David

 

[Bob Phillips]

That is the whole code for what you asked, it shows the form and it
populates C4 on OK. Anything else depends upon what you are doing elsewhere.

--

HTH

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

 

[Nigel]

Include the Userform1.Show command in the workbook open event

Private Sub Workbook_Open()
Userform1.Show
End Sub

The only other item to consider is to unload the form after updating the
sheet
so a mod to Bob Philips code should include.....

Private Sub cmdOK_Click()
Worksheets("Sheet1").Range("C4").Value = Textbox1.Text
Unload Me
End Sub

Since the code for showing the form is part of your application workbook and
the form code is part of the form (in the same workbook), you should not
have anything in your personal.xls - unless you have other code? if so move
this to a module into your application workbook.

Cheers
Nigel