How to make a Macro "On/Off"

[bobsburgers]

Hi, all -

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

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

 

[Shortmeister1]

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.

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.

 

[Shortmeister1]

I'd also suggest that this is bad coding as it's better to work out the exact conditions and code for it.

 

[bobsburgers]

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

 

[bobsburgers]

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.

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"

 

[Rick Rothstein]

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.

 

[bobsburgers]

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!!

 

[Shortmeister1]

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

 

[bobsburgers]

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

 

[NoSparks]

I think this will work until there is no scan or you click cancel or close on the input box.

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