How to run a maco only once

mtampa

Board Regular
Joined
Oct 15, 2007
Messages
61
Hi guys,

Last week the board helped me figure out how to run a macro based on the selection from a combo box using the following code:

Sub Test_K3()
If Range("K3").Value = 3 Then Call FOC
If Range("K3").Value = 4 Then Call ExtParty
If Range("K3").Value = 2 Then Call Vacation
End Sub

I was able to get my macro FOC to run when the user selected choice 3 (a second combo box appears from a hidden area). However if the user tinkers with the form and happens to select option 4, then come back to option 3, my box continues its path across the screen.

I am trying to figure out the following order of operations:

1 - If the initial selection is option 3 then the box appears. (This I can do using the below macro:

Sub FOC()
'
' FOC Macro
' Macro recorded 10/11/2011 by tampami
'

'
Rows("28:79").Select
Selection.EntireRow.Hidden = False
Rows("12:28").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("Drop Down 47").Select
Selection.ShapeRange.IncrementLeft -522#
Selection.ShapeRange.IncrementTop 3.75
Range("K2").Select
End Sub

2 - If the choice is changed to choice 2 or 4, the box returns to its original position (This I can do with a macro called "return_box" ...see below:

Sub RETURN_BOX()
'
' RETURN_BOX Macro
' Macro recorded 10/18/2011 by tampami
'

'
ActiveSheet.Shapes("Drop Down 47").Select
Selection.ShapeRange.IncrementLeft 522#
Selection.ShapeRange.IncrementTop -3#
Range("K2").Select
End Sub

3 - However, if choice 2 or 4 is chosen initially, I do not want the macro "return_box" to trigger.

In summary, I want "return_box" to run ONLY if choice 3 was selected initially.

How in the world would this be programmed?

Thanks!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Code:
Sub Test_K3()
    Select Case Range("K3").Value
        Case 3
            Call RETURN_BOX
            Call FOC
        Case 4
            Call ExtParty
        Case 2
            Call Vacation
    End Select
End Sub

Or just combine the two macros RETURN_BOX and FOC
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top