"Pause" Checking ...

[tonyrosen]

Let's say we have a macro:


Sub MyMacro()
Dim i As Integer
Dim rg As Range

For i = 1 To 15
rg = Range("Range" & i)
' Check if the range is empty
' If it is, "stop" here and send them back
' If it isn't, go on to the next rg

' If we "stopped", how can we start back where we left off?
Next i
End Sub


If we "stopped", how can we start back where we left off?

[tonyrosen]

This is what I came up with. There's GOT to be an easier way.


Private Sub CheckRange()
Dim bTrueFalse As Integer
Dim iContinue As Integer
Dim iNext As Integer
Dim sMsgBox As String
' 1 is TRUE - it is filled out
' 0 is FALSE - it is not filled out
' assume it's filled out
bTrueFalse = 1
For iContinue = 1 To 13
' If Trim(Range("Range" & iContinue).Text) = "" Then
If IsEmpty(Range("Range" & iContinue)) Then
bTrueFalse = 0
Else
bTrueFalse = 1
End If
If bTrueFalse = 0 Then
Select Case iContinue
Case 1
MsgBox ("Please fill in the required field: [SUBJECT]")
Range("Range" & iContinue).Select
Exit Sub
Case 2
MsgBox ("Please fill in the required field: [FACILITATOR]")
Range("Range" & iContinue).Select
Exit Sub
Case 3
MsgBox ("Please fill in the required field: [DATE]")
Range("Range" & iContinue).Select
Exit Sub
Case 4
MsgBox ("Please fill in the required field: [PARTICIPANTS]")
Range("Range" & iContinue).Select
Exit Sub
Case 5
Range("Range" & iContinue).Select
Case 6
MsgBox ("Please fill in the required field: [FUNCTIONAL AREA]")
Range("Range" & iContinue).Select
Exit Sub
Case Else
Range("Range" & iContinue + 1).Select
Exit Sub
End Select
Exit Sub
Else
iNext = iContinue + 1
Range("Range" & iNext).Select
End If
Next iContinue
End Sub

[Zack Barresse]

Maybe I'm not following, but you're looking to loop through a range, come upon the first empty cell in that range and select it. If you run it again, come to the first empty cell and select it, which may vary. Is this correct?

Also, I'm assuming these are range names?

[tonyrosen]

Not expressly. What I want to do is:

1) Check to see if a required field is empty
A) It's Empty: Popup and select
B) It's Filled: select next range

Rewrite from an answer in another place:


Private Sub CheckRange()
Dim iContinue As Integer
Dim iNext As Integer
For iContinue = 1 To 13
If IsEmpty(Range("Range" & iContinue)) Then
Select Case iContinue
Case 1
ShowMsg "[SUBJECT}", iContinue
Case 2
ShowMsg "[FACILITAR]", iContinue
Case 3
ShowMsg "[DATE]", iContinue
Case 4
ShowMsg "[PARTICIPANTS]", iContinue
Case 5
Range("Range" & iContinue).Select
Case 6
ShowMsg "[FUNCTIONAL AREA]", iContinue
Case Else
Range("Range" & iContinue + 1).Select
End Select
Exit Sub
Else
iNext = iContinue + 1
Range("Range" & iNext).Select
End If
Next iContinue
End Sub
Sub ShowMsg(sMsg As String, iCon As Integer)
MsgBox ("Please fill in the required field: " & sMsg)
Range("Range" & iCon).Select
End Sub

[Killian]

If you set the range name of each cell with it's field name e.g.
SUBJECT
FACILITATOR
etc...

then name the range "rngToCheck", you can loop through each and use the range name to return the entry field Dim rng As Range

For Each rng In Range("rngToCheck")
If IsEmpty(rng) Then
MsgBox "Please fill in the required field: " & rng.Name.Name
rng.Select
Exit For
End If
Next

[tonyrosen]

K,

That's not entirely possible because of "company standards" on spreadsheets which MAY one day be added to a database ... they don't want to rewrite their .NET code, I guess.

[Killian]

K,

That's not entirely possible because of "company standards" on spreadsheets which MAY one day be added to a database ... they don't want to rewrite their .NET code, I guess.
How incovenient...
I'm sure they've got thier reasons... and who wants to use descriptive range names anyway? That would make spreadsheet/code maintenance far too easy :think:

So you need to store the stirngs for the field names somewhere, that leaves you with a select case like you posted last or sorting them in another range (source data sheet, hidden sheet or something allowed by "company standards"). In that case you could use a similar loopDim i As Integer

For i = 1 To 9
If IsEmpty(Range("Range" & i)) Then
MsgBox ("Please fill in the required field: " & _
Sheets("LookUp").Cells(1, i).Text)
Range("Range" & i).Select
Exit For
End If
Next i