How to make a Macro "On/Off"

bobsburgers

Board Regular
Joined
Jun 25, 2017
Messages
60
Hi, all -

Would it be possible to turn to below code into a Macro that repeats its core function until told to stop?

Code:
Sub Item_Return()


    Dim scanstring As String
    Dim foundscan As Range
    Dim ws As Worksheet
    Dim foundscan_address As String
    
Set ws = ActiveSheet


scanstring = InputBox("Please enter a value to search for", "Enter value")


With ws.Columns("D")
    
    Set foundscan = .Find(What:=scanstring, LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)
                      
    If Not foundscan Is Nothing Then
foundscan_address = foundscan.Address

Do
        foundscan.Offset(0, 4).Value = scanstring
        ws.Activate
        foundscan.Activate
        ActiveWindow.ScrollRow = foundscan.Row

        Set foundscan = .FindNext(foundscan)

Loop While Not foundscan Is Nothing And foundscan.Address <> foundscan_address

    Else
        MsgBox scanstring & "  was not found"
    End If
    
End With

End Sub

Ideally, it would be run with an "On/Off" button; wherein, while the Macro is "On", the function resets every time it is performed, until the user turns the macro "Off".

Thank you!

Best,

Bob
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
You could always use the DoEvents command.

As an example, put a zero in Cell A1 in a blank sheet and run the following code.

Code:
Option ExplicitSub test()
Dim i As Variant
For i = 1 To 100000000
DoEvents
    If Cells(1, 1) = 1 Then
        MsgBox "stopped"
        Exit Sub
    End If
    Application.StatusBar = i
Next i
MsgBox "Normal end"
End Sub

With DoEvents enabled, you have access to the application.
 
Upvote 0
I'd also suggest that this is bad coding as it's better to work out the exact conditions and code for it.
 
Upvote 0
I'd also suggest that this is bad coding as it's better to work out the exact conditions and code for it.

I would love to code for this condition...unfortunately I don't speak the language well enough to get into the details that I really want to achieve.

Is there a way to write the "ongoing function" into the code that I provided?

Thank you!

Best,

Bob
 
Upvote 0
You could always use the DoEvents command.

As an example, put a zero in Cell A1 in a blank sheet and run the following code.

Code:
Option ExplicitSub test()
Dim i As Variant
For i = 1 To 100000000
DoEvents
    If Cells(1, 1) = 1 Then
        MsgBox "stopped"
        Exit Sub
    End If
    Application.StatusBar = i
Next i
MsgBox "Normal end"
End Sub

With DoEvents enabled, you have access to the application.

When I run this a dialogue box appears that reads:

"Compile Error: Expected: End of Statement"
 
Upvote 0
Would it be possible to turn to below code into a Macro that repeats its core function until told to stop?
Can you tell us in words what you are trying to get your code to do? Not the on/off part you asked about, rather, what is the ultimate goal of this code once it successfully finishes.
 
Last edited:
Upvote 0
Can you tell us in words what you are trying to get your code to do? Not the on/off part you asked about, rather, what is the ultimate goal of this code once it successfully finishes.

Absolutely!

I would like to be able to press the Item Return button and have the dialogue box stay open until it is manually closed or canceled. This would be to return multiple items without have to press the "item return" button every time.

We have "No Charge Forms" that are made up of multiple items (denoted by their SKUs); when a "No Charge" is returned, it is usually multiple items, and ultimately, I would like to press the "item return button," have the dialogue box come up, enter the search string, run the function, and then have the dialogue box come back up without having to press the button again. Ideally, the functions would stop when the user presses cancel on the dialogue box.

I've been doing some research, would a "Do...loop" statement work?

Thank you so much!!
 
Last edited:
Upvote 0
HI Bob - sorry for the delay.

The reason that sub isn't working is that there's a typo in the first line. Insert a carriage return between "Option Explicit" and "Sub Test"


Apologies!
Martin
 
Upvote 0
HI Bob - sorry for the delay.

The reason that sub isn't working is that there's a typo in the first line. Insert a carriage return between "Option Explicit" and "Sub Test"


Apologies!
Martin

No apologies necessary! Any input helps; however, how would I insert a "carriage return" within the VBA code?

I'm not exactly sure how a carriage return would work in the code. Thank you so much for your help and patience!!

Best,

Bob
 
Upvote 0
I think this will work until there is no scan or you click cancel or close on the input box.
Code:
Sub Item_Return()

    Dim scanstring As String
    Dim foundscan As Range
    Dim ws As Worksheet
    Dim foundscan_address As String
    
Set ws = ActiveSheet

scanstring = InputBox("Please enter a value to search for", "Enter value")
If scanstring = "" Then Exit Sub

With ws.Columns("D")
    
    Set foundscan = .Find(What:=scanstring, LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)
                      
    If Not foundscan Is Nothing Then
foundscan_address = foundscan.Address

Do
        foundscan.Offset(0, 4).Value = scanstring
        ws.Activate
        foundscan.Activate
        ActiveWindow.ScrollRow = foundscan.Row

        Set foundscan = .FindNext(foundscan)

Loop While Not foundscan Is Nothing And foundscan.Address <> foundscan_address

    Else
        MsgBox scanstring & "  was not found"
    End If
    
End With

    Call Item_Return

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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