Looping within VBA MsgBox

Question

I have some back-end code that produces a MsgBox depending on the content of a front-end user editable sheet ("iButtons"). The below code works fine and pretty quickly but I am conscious of the fact that as the "iButtons" sheet grows I will need to unnecessarily manage this code over time.

Could I easily write this into a loop that quickly scans the iButtons sheet for all rows on the sheet that has content (up until an empty row has been reached)? Would the loop be fast enough to be run passively in the background or would the response of the MsgBox pop-up be excessively slow given the amount of code that will need to be run?

Public Sub iButtons(ByVal Target As Range)

'Pulls content from the iButtons sheet
     If (ActiveSheet.Name = Worksheets("iButtons").Range("A4")) And Not Intersect(Target, Target.Parent.Range(Worksheets("iButtons").Range("B4"))) Is Nothing Then
        MsgBox Worksheets("iButtons").Range("D4"), vbInformation, Worksheets("iButtons").Range("C4")
        ActiveSheet.Range("A1").Select
    End If

     If (ActiveSheet.Name = Worksheets("iButtons").Range("A5")) And Not Intersect(Target, Target.Parent.Range(Worksheets("iButtons").Range("B5"))) Is Nothing Then
        MsgBox Worksheets("iButtons").Range("D5"), vbInformation, Worksheets("iButtons").Range("C5")
        ActiveSheet.Range("A1").Select
    End If

     If (ActiveSheet.Name = Worksheets("iButtons").Range("A6")) And Not Intersect(Target, Target.Parent.Range(Worksheets("iButtons").Range("B6"))) Is Nothing Then
        MsgBox Worksheets("iButtons").Range("D6"), vbInformation, Worksheets("iButtons").Range("C6")
        ActiveSheet.Range("A1").Select
    End If

End Sub

Thanks!

It is easier to loop with `Cells` (e.g. `Range("A4")` is `Cells(4,1)`, and `Range("D6")` is `Cells(6,4)`) - and if you only want 1 message box then you can use `Exit For` once you have a "hit" — Chronocidal, Mar 20 '19 at 11:10
You can use `MATCH` in the column A on iButtons to find the row to a variable, say `lngRow`, based on `activesheet.name` then use `Cells(lngRow,4)` as suggested. — Nathan_Sav, Mar 20 '19 at 11:31
@Chronocidal That approach worked exactly as I needed, converted all the Ranges to Cells and wrote a loop to incrementally increase the values per loop. Thanks! — tboo132, Mar 21 '19 at 10:33

score 0 · Answer 1 · answered Mar 20 '19 at 13:26

A programmatic aproach could be (sorry my VB is a bit rusty today, so generic description only):

Make a global array of information about each button and initialize the array with the informaton about each button (the information seems standard, so you can use an array of user defined Type).

With each button that is added or removed from the sheet, only update this array.

Have a procedure to process all buttons. It has a loop that goes from the first entry of the array (a button) to the last button in the array and performs a generic processing.

Looping within VBA MsgBox

1 Answers1