Solved: Run macro from cell

[xluser2007]

Hi All,

I have the relevant sample file as attached.

In the first worksheet, I am running a macro from a button loaded from the Control toolbar.

In the second worksheet, rather than running from a formal button, I would like to run the macro from the user single-clicking the yellow named range "run_displaydatetime" (in cell (B3).

How would I go about doing this?

[Bob Phillips]

[vba]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Me.Range("B3")) Is Nothing Then

Call MyMacro
End If
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[xluser2007]

Works well Bob, as always you are a great help.

Bob, to take it further, the background for this question is concerned with good VBA based spreadsheet design.

Basically, I've written a workbook, which is a master control for a bunch of macros that the user calls from a series of command buttons.

It all works fine, but I find that everytime I add a new macro, I have to create an associated command button and write relevant code that will call on macros (with the associated error handling) for that button.

I figured that using cells (as opposed to command buttons) to call relevant macros would be easier because:

1. Don't have to create a separate ActiveX object to run the same type of macros i.e. Command button.
2. Can print relevant log details after a macro is run next to cells (using offset functions in VBA) more easily, where as this is a problem with command buttons if you move them.
3. Like command buttons they can be defined with a unique name i.e. as a named range so as to more logically identify them.
4. It would probably be easier to call macros from all relevant cells on a worksheet using a single worksheet_change event as opposed to creating code for each command button (the biggest advantage I'm thinking of).

I don't know if I'm thinking this correctly for long term usage and best practice spreadsheet design.

One of the cons I can see of not using command buttons, is that I don't know how to make the cells look more attractive like a button.

Do you have any thoughts on the above, as I would like to use cells instead of buttons going forward, for at least this large master macro workbook that I am using.

Also do you have any examples/ templates of workbooks that use cells instead of buttons that look polished? My cell decoration skills are not that great .

Thanks for your help. I'll mark the original problem solved, but would like tis discussion to continue as part of my learning process.

Thanks,

[Bob Phillips]

To be perfectly honest, I wouldn't use buttons on a spreadsheet or cells to drive a macro, but I wwould add a menu item (commandbars). If you have a table driven menu, it is easy to just add a new line in the table with the details, and then regenerate the menu, and your new macro is readily available.

[xluser2007]

Bob, a really good flexible idea.

If I understand you correctly, I assume you mean that the command bar would be unique only to the relevant workbook from which the macros would be called and not as a generic addin.

Would you have a sample example of this availible for me to understand please?

[Bob Phillips]

It could be either mate, the macros are available to all workbooks when the menu is invoked, and you would code any constraints that you want. Even an addin can have a worksheet with the table on it, although of course you would have to change the IsAddin property to False to make it visible to change, and then set back to TRue.

Here is a little example I just knowcked up using by table routine.

[xluser2007]

Thanks for your prompt reply Bob.

Look forward examining your workbook. I can;t seem to view it as it has a password lock on it?

[Bob Phillips]

Sorry about that, I cobbled it from one of my applications and forgot to remove it. THis should be okay now.

[xluser2007]

No worries, I appreciate your efforts!

This is very elegant Bob. i will be definitely making use of this going forward.

Upon reflection though, using cells (or buttons) may have a good purpose for the original exercise that i had suggested.

Basically because each worksheet could stand for a specific purpose and the relevantly labelled buttons would guide the end user for the action to take.

Also it may be easier to pack in background info and macro log details in worksheets rather than using a menu item.

This tool you have provided is awesome for general add-in (*.xla) command bar design.

Thanks again for your help. If you ahve any further thoughts or suggestions please let me know.

[Bob Phillips]

I just generally have an aversion to putting buttons on the worksheet, I see that as the place for data. But I did once create a workflow type application where I put lots of buttons on a worksheet, and I had flow lines between them, and I enabled/disabled them as they proceeded through the flow. For instance, it would start at button A, all others disabled. They would run the button A procedure, and that also disabled button A and enabled button B, and so on. Further down, there might be a course of actions, so you enable a number of buttons and then take the path of the one that they choose. And I adde textboxes as narrative.

Is that the sort of thing that you mean?

BTW, what do you mean by macro log details?

[Bob Phillips]

One other thing to note is that you can hide/unhide menu options conditionally. So for instance, you could have a series of menu options that only apply to a particular worksheet, so you could have application events listening for that worksheet to be activated, and unhiding those menu options when it does so.

You can also create right-click menu additions which is sometimes a nice touch.

[xluser2007]

I just generally have an aversion to putting buttons on the worksheet, I see that as the place for data. But I did once create a workflow type application where I put lots of buttons on a worksheet, and I had flow lines between them, and I enabled/disabled them as they proceeded through the flow. For instance, it would start at button A, all others disabled. They would run the button A procedure, and that also disabled button A and enabled button B, and so on. Further down, there might be a course of actions, so you enable a number of buttons and then take the path of the one that they choose. And I adde textboxes as narrative.

Is that the sort of thing that you mean?

Bob, that sounds like a very flash and trendy version of what I have done and would aim to ideally achieve with this application!

Basically, I have a set of fixed buttons on worksheets, which are arranged in order, and labelled according to their relevant process/ macro.

For example, a "parameters" worksheet comes first and the user thus updates the relevant dates (using data validation) and various rates and factors in the cells and presses the first macro button. A "macro log" is printed next to the button (see below for more details on this).

The next button, which would be just below it, "logically flows" on (ideally) as the next step, is then to be clicked by the user. Once all buttons are clicked, the user assumes that they move to the next worksheet and the process continues.

Your hiding buttons in order is almost a process-validation in itself, very cool!

This project has saved a lot of time, but the VBA behind it could be better coded and the "flow" design could be improved, hence this thread was opened and our fruitful discussion has followed. In fact the original version was developed by skills learnt here at VBAX and at mrExcel.com.

BTW, what do you mean by macro log details?

This is just a few lines, an audit trail printed next to a macro button, after the macro that it it is linked to has been run by the user. Just lets the user know when the macro mwas run, and briefly, what it achieved. This was done using a clunky manual method with command buttons, where I put the buttons in place e.g. cell J3, and then in VBA had to manually set the log to print in L3, just to the right of the button.

Of course you can imagine how much problem it caused whn I wanted to move buttons and the log was not dymanic with the movement!

(This was one of the reasons I was suggesting using cells as buttons, epecially named ranges), as its easier to offset the log with respect to a easily movable named range.

One other thing to note is that you can hide/unhide menu options conditionally. So for instance, you could have a series of menu options that only apply to a particular worksheet, so you could have application events listening for that worksheet to be activated, and unhiding those menu options when it does so.

You can also create right-click menu additions which is sometimes a nice touch.

This, like the above sounds brilliant mate.

I would love to learn, but am not sure how to do this advanced stuff.

Would you have any simple examples of the above? Please note, you have helped me so much, I don;t want to trouble you, just if you've got a quick sample tucked away for the above suggestions somewhere I would love to see those.

Again, I know you are flat out as is and appreciate all the help and feedback you've offered as per the above, but if you could show me that would be super!

No rush with the above for me personally, just great to learn this neat stuff.

[Bob Phillips]

Bob, that sounds like a very flash and trendy version of what I have done and would aim to ideally achieve with this application!

Basically, I have a set of fixed buttons on worksheets, which are arranged in order, and labelled according to their relevant process/ macro.

For example, a "parameters" worksheet comes first and the user thus updates the relevant dates (using data validation) and various rates and factors in the cells and presses the first macro button. A "macro log" is printed next to the button (see below for more details on this).

The next button, which would be just below it, "logically flows" on (ideally) as the next step, is then to be clicked by the user. Once all buttons are clicked, the user assumes that they move to the next worksheet and the process continues.

This all sounds very similar to the one I created

This is just a few lines, an audit trail printed next to a macro button, after the macro that it it is linked to has been run by the user. Just lets the user know when the macro mwas run, and briefly, what it achieved. This was done using a clunky manual method with command buttons, where I put the buttons in place e.g. cell J3, and then in VBA had to manually set the log to print in L3, just to the right of the button.

Of course you can imagine how much problem it caused whn I wanted to move buttons and the log was not dymanic with the movement!

(This was one of the reasons I was suggesting using cells as buttons, epecially named ranges), as its easier to offset the log with respect to a easily movable named range.

This is a nice idea, but I can see th difficulty. I have some code that adds tooltip text to ACtiveX buttons, perhaps that would be more flexible.

This, like the above sounds brilliant mate.

I would love to learn, but am not sure how to do this advanced stuff.

Would you have any simple examples of the above? Please note, you have helped me so much, I don;t want to trouble you, just if you've got a quick sample tucked away for the above suggestions somewhere I would love to see those.

Again, I know you are flat out as is and appreciate all the help and feedback you've offered as per the above, but if you could show me that would be super!

No rush with the above for me personally, just great to learn this neat stuff.

I knock up a modified version of the workbook to demonstrate it, and post it soon.

[Bob Phillips]

Here is a quick example of the dynamic menus.

[xluser2007]

Here is a quick example of the dynamic menus.

Very nice Bob, I see that you need to reset the menu and the dynamic changes flow through as you move from sheet to sheet.

This is very cool indeed. Just goes to show how much there is to learn here !

This is a nice idea, but I can see th difficulty. I have some code that adds tooltip text to ACtiveX buttons, perhaps that would be more flexible.

Bob, wasn't quite sure what you meant by this. What is Tooltip text? Sounds interesting.

[Bob Phillips]

Tooltiptext is when you hover over a button, a little popup appears with some text. YOu can get them on userform controls, and it is a bit like a cell comment. I was thinking you could put that audit info in the text. You would have to program it in, but that is no problem.

[xluser2007]

Hi Bob,

Just with a recent project I'm doing, I have decided to use the method you had shown earlier about running a macro by double clicking a single_cell.

In this particular workbook, master.xls, in a particular worksheet, I have 7 such buttons (i.e. cells), for 7 clients, from which the user can double-click to run the relevant macro for that client.

Each button (i.e. cell) is a named range for that particular client.

Using the Worksheet_BeforeDoubleClick event, I have the following code for one client e.g. client ABC:

[vba]Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Me.Range("Copy_ABC_Assumptions_FOM09")) Is Nothing Then

Select Case MsgBox("Are you sure you want to copy the assumptions files over from Transition Model Jun08?, are you sure you want to continue?", vbYesNo Or vbExclamation Or vbSystemModal Or vbDefaultButton1, "Copy Transition Jun08 Assumptions for ABC?")

Case vbYes

' Call Copy_Multiple_Files(ThisWorkbook.Range("Start_Filecopy_ABC"))

Call dummy

Me.Range("Copy_ABC_Assumptions_FOM09").Offset(0, 1).Value = "<<< This macro run at, " & Format$(Now(), "dd/mm/yyyy hh:mm:ss")

Me.Range("Copy_ABC_Assumptions_FOM09").Offset(0, 1).Activate

Case vbNo

Me.Range("A1").Activate

Exit Sub

End Select

End If

End Sub[/vba]
For client DEF, whose named range button is "Copy_DEF_Assumptions_FOM09" also has another relevant named range used by the macro above "Start_Filecopy_DEF".

If I wanted to do exactly the same code for client DEF etc do I have to copy paste and change the references in the above Worksheet_BeforeDoubleClick code, or is there a slicker way to combine all codes into the single Worksheet_BeforeDoubleClick event?

Also, what sort of error handling could be added to make it more robust?

regards

[Bob Phillips]

All you would do is test for each range

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Me.Range("Copy_ABC_Assumptions_FOM09")) Is Nothing Then

'your code for this range
ElseIf Not Intersect(Target, Me.Range("Start_Filecopy_DEF")) Is Nothing Then

'your code for this other range
'etc
End If

End Sub
[/vba]

but if they all process exactly the same you can use

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Me.Range("Copy_ABC_Assumptions_FOM09")) Is Nothing Or _
Not Intersect(Target, Me.Range("Start_Filecopy_DEF")) Is Nothing Then

'your code for these ranges
'etc
End If

End Sub
[/vba]

In your example, I think you would use the latter style, and adjust the ranges like so

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range

Select Case True

Case Intersect(Target, Me.Range("Copy_ABC_Assumptions_FOM09")) Is Nothing

Set rng = Me.Range("Copy_ABC_Assumptions_FOM09")

Case Intersect(Target, Me.Range("Start_Filecopy_DEF")) Is Nothing

Set rng = Me.Range("Start_Filecopy_DEF")

Case Else

Exit Sub
End Select

Select Case MsgBox("Are you sure you want to copy the assumptions files over from Transition Model Jun08?," & _
"are you sure you want to continue?", _
vbYesNo Or vbExclamation Or vbSystemModal Or vbDefaultButton1, _
"Copy Transition Jun08 Assumptions for ABC?")

Case vbYes

' Call Copy_Multiple_Files(ThisWorkbook.Range("Start_Filecopy_ABC"))

Call dummy

rng.Offset(0, 1).Value = "<<< This macro run at, " & Format$(Now(), "dd/mm/yyyy hh:mm:ss")

rng.Offset(0, 1).Activate

Case vbNo

Me.Range("A1").Activate

End Select

End Sub
[/vba]

Of course, if those ranges were single cells, you could just Target throughout

[xluser2007]

Bob, many thanks for your thoughtful reply. Its starting to become clearer now.

Slight change to the way the macro is run though. If the user double-clicks on the named range in the activeworksheet "Copy_ABC_Assumptions_FOM09", then we should run

[vba]Call Copy_Multiple_Files(ThisWorkbook.Range("Start_Filecopy_ABC"))[/vba]
Alternatively, if the user clicks on the named range "Copy_DEF_Assumptions_FOM09", then we should run

[vba]Call Copy_Multiple_Files(ThisWorkbook.Range("Start_Filecopy_DEF"))[/vba]
etc.

Notice that the named range for double-clicking for the client e.g. ABC, also has a macro "Copy_Multiple_Files" run for a different named range cotaining that client i.e. "Start_Filecopy_ABC".

How would I Integrate it into your elegant code as per above.

Also, one other query, If we are using Select Case True, then when we set the "Rng" object, should we be testing as follows:

[vba] Select Case True

Case Not Intersect(Target, Me.Range("Copy_ABC_Assumptions_FOM09")) Is Nothing

Set rng = Me.Range("Copy_ABC_Assumptions_FOM09") [/vba]
Just wanting to clarify, as I was a bit confused.

Also, is there any nice error handling that we can put in for this event-driven code?

Thanks for your kind and continued help for my queries Bob .

[xluser2007]

Hi Bob,

I was just wondering what you thought of the queries above?

I know you are super busy, but if you could please help me complete this, it would be awesome. The stuff you've shown above has been really great for my understanding.

regards,