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

 

[TotallyConfused]

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

Hi Bobsburgers

I think what Martinshort is trying to tell you to do, is place your cursor between the words ‘Option Explicit’ and ‘Sub Test()’ and then press your ‘ENTER’ key. This will move the words ‘Sub Test()' down one line. In coding, the ‘ENTER’ key is sometimes referred to as a ‘Carriage Return’. What you should see would look like this:
Option Explicit
Sub Test()
‘now the rest of the code
End Sub

 

[Shortmeister1]

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

Just press Enter

 

[bobsburgers]

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

This seems to work perfectly! Thank you so much NoSparks!!

I'll test it at full volume today!

As always, thank you all so much for your help with this!!

Best,

Bobo

 

[bobsburgers]

Just press Enter

I just saw these responses! I'm sorry for the delay myself!!

This variation works quite well too, thank you!

Also, this Sub Test is very interesting code to have and try to understand, so thank you for that too!

Best,

Bobs

 

[bobsburgers]

Hi Bobsburgers

I think what Martinshort is trying to tell you to do, is place your cursor between the words ‘Option Explicit’ and ‘Sub Test()’ and then press your ‘ENTER’ key. This will move the words ‘Sub Test()' down one line. In coding, the ‘ENTER’ key is sometimes referred to as a ‘Carriage Return’. What you should see would look like this:
Option Explicit
Sub Test()
‘now the rest of the code
End Sub

Hi, TotallyConfused!

thank you so much for your response on this. I wasn't exactly clear on how this coding was supposed to work with the code I already have, so this helped a lot. thanks again!!

best,

bob

 

[TotallyConfused]

Hi, TotallyConfused!

thank you so much for your response on this. I wasn't exactly clear on how this coding was supposed to work with the code I already have, so this helped a lot. thanks again!!

best,

bob

You're welcome. Just glad to be of help. It's the little details that can mess a person up. Since I joined this site, there have been several people help me with my problems, so any time I can return the favor, I'm glad to do so.