VB in Excel 1

[gxd135]

Hello all,
I have recorded a couple of macros in excel to do various things. What I now want to do is run 1, 2, 3 or all of them if a certain cell value is greater than a certain number. What i have is a value in cell c5. If this value is greater than 5 I want to run the first macro. If the value is greater than 10 I want to run the second macro, and if the value is greater than 15 I want to run the third, and so on and so on. I am a beginner at writing VB code, but I know that this is pretty simple, I just don't know exactly what to do.

Any help would be greatly appreciated.

G

 

[SkipVought]

Hi,
Is it ALWAYS the value in C5?
If it is then in the Worksheet_Change Event, enter your code...

    With Target
        If .Row <> 5 Then Exit Sub
        If .Column <> 3 Then Exit Sub
        Select Case .Value
            Case Is > 15
                MsgBox &quot;Macro3&quot;
            Case Is > 10
                MsgBox &quot;Macro2&quot;
            Case Is > 5
                MsgBox &quot;Macro1&quot;
        End Select
    End With

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

The Worksheet_Change_Event?

I am very new to VB. Do I copy this code into a macro?

Yes it is always in cell C5. this is on the second worksheet, and I want to run the macros if the value in c5 is greater than 5 or 10 and so on. I copied the code into a new macro and ran it and got a run error. I did change the " Macro1" to the right names, but i am not sure if I am supposed to go into a macro or where I put the code. Thanks for the quick response,

G

 

[SkipVought]

In the VBE look at the Project Browser.
Sheet2 has code associated with it. Right click on the Sheet2 object and select View Code.
In the code window are 2 dropdowns, Object and Procedure. From the Object drop down select Worksheet. From the Procedure drop down select Change event.
In WorkSheet_Change, past the code I gave you.

hope this helps Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

You get to the VBE by alt+F11
To get the Project Explorer/Browser - MenuItem View/Project Explorer

Then continue with the instructions above. Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

I did what you said, and its not working. Sorry to be a pain, but I changed the code to fit my needs. Here it is First_Copy is the first macro, and second copy the second macro. If c5 is greater than 5 I run the macro First_Copy, if greater than 10 I run macro second_Copy. Any other suggestions. The object is worksheet and the procedure is set to change. Will this run the macros on any change in the sheet. If so I would like to set it up to a macro that runs when a button is pushed. THANKS FOR THE HELP.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row <> 5 Then Exit Sub
If .Column <> 3 Then Exit Sub
Select Case .Value
Case Is > 10
MsgBox &quot;First_Copy&quot;
MsgBox &quot;Second_Copy&quot;
Case Is > 5
MsgBox &quot;First_Copy&quot;
End Select
End With
End Sub

 

[SkipVought]

When a value is entered in row 5 column 3 the change is evaluated via the code. No other change on the sheet causes anything else to happen.

Tell me exactly what happens when you enter a value in row 5 column 3.

What you are showing me is that First_Copy AND Second_Copy will run if value > 10. Is that what you want?

Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

yes, if the value is greater than 10 i want to run first copy and second copy. if greater than 5 just first copy. But, I don't want it to run everytime c5 is changed because the user is also changing other values. I need all the values to be changed, then run the code. Thanks

 

[SkipVought]

Well that's NOT what you orginally asked for.

1. Is the code working? You did not answer ALL my questions if it is NOT.

2. To Add a CommandButton, right-click on the Excel toolbar - you will get a popup menu - select Control Toolbox.

3. In the Control Toolbox window select Command Button - your mouse pointer changes to cross-hairs - on your sheet, insert button

4. Double click on Command Button to view the code for the CommandButton1_Click event. Paste the code here (obvoiusly, remove the code from the Worksheet_Change event)

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

Sorry,

The I made a button and pasted the code. When I click the button, i get a run error and click debug and &quot;If .Row <> 5 Then&quot; is highlighted in yellow. So the code is not working. Help!

 

[SkipVought]

Yes, it's true. .Row has Target as it object which is not present in the CommandButton_Click event.

for Target substitute, Cells(5,3)

Sorry Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

OK, so now it works but all it does is make a message box. I want it to run a macro, not pop up a message box. Can I substitute MsgBox with Macro or something like that. I want to run macros which I have created.

 

[SkipVought]

That's EXACTLY what you do, substitute the MacroName for the MsgBox line.

It is common programming practice to write code that has various conditions stubbed off. That way, you can be sure that certain control code is behaving properly.

Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

OK,

IT WORKS

One more Question, then I promise I won't bother you anymore. If I want one of the cases to run two macros do I put:

Case Is > 10
Macro1
Macro2

Or

Case Is > 10
Macro1 & Macro2

What is the syntax for calling two macros.

THANKS SKIP

 

[SkipVought]

Use example 1
Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

Right, but Macro 3 runs off of Macro 2 which runs off Macro 1. So 1 must execute in order for two to work. So can I say

Case Is > 15
Macro1
Macro2
Macro1

I know I can say

Case Is >15
Macro1
Case Is >15
Macro2
Case Is >15
Macro3

But is there an easier way

THANK YOU

 

[SkipVought]

WHAT?
What do you mean that Macro3 runs off Macro2 etc???? Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

Macro1 copies something into certain cells, and Macro 2 copies that and uses that info in it. Therefore if Macro 1 does not run, Macro 2 is useless and does nothing.

 

[SkipVought]

So what you want is....

if value > 5 then macro1
if value > 10 then macro2
if value > 15 then macro3

Skip,
metzgsk@voughtaircraft.com

 

[gxd135]

Something like that, but,

can it be

If value >5 then macro1
If value >10 then macro1 and macro2
If value >15 then macro1 and macro2 and macro3
If value >20 then macro1 and macro2 and macro3 macro4

The reasoning is because macro 1 must run before macro 2 and 3 and so on.

macro2 and macro1 must both run before 3 and so on.