Excel Macro VBA Function 1

[Mike555]

I have an Excel spreadsheet containing a command button with the following syntax-

Application.Dialogs(xlDialogSendMail).Show


This code creates a blank e-mail and attaches the excel file. Cell A1 in my spreadsheet contains the email address of the recipient. Is there a way to automatically populate the 'To' field in the e-mail with this data?

 

[Mike555]

Nevermind...I found out. The answer is:
Application.Dialogs(xlDialogSendMail).Show ActiveSheet.Range("a1&quot.Value

 

[VBAjedi]

Mike555,

I have a FAQ posted on this subject. Uses a little different approach with more code, but gives you more control as well!

faq707-3564

Hope that helps.

VBAjedi

 

[Mike555]

This is good info. Very more flexible too...Thanks.

I have an additional question which I'm hoping you know the answer to:

In Excel, how can you disable a command button after it is used? I know that in access you could set the Enable property to False, but not sure how to do this in Excel.

 

[TonyJollans]

Hi Mike,

It's generally better to start a new thread with a new question but, for what it's worth, here's what I know on the subject.

If it's a Command Button (from the Control Toolbox) then it's just the same: at the end of your code, set the Enabled property to False.

If it's a Button from the Forms Toolbar, it's not so easy. These ones don't have an Enabled property but you could set the Visible property to False to remove it from view if that meets your needs.

Enjoy,
Tony

 

[Loomah]

Hi
The property you're actually looking for with the command button is 'TakeFocusOnClick' which is True by default - set it to false.

For controls that don't have this property I tended to use the increadibly inelegant line 'Range("whatever&quot.Activate' to change focus at the end of the event procedure.

;-)

If a man says something and there are no women there to hear him, is he still wrong?