Wilconcl51
New Member
- Joined
- Oct 10, 2023
- Messages
- 26
- Office Version
- 2016
- Platform
- Windows
This is my code and for the life of me I am unable to have the outer loop work.
Rich (BB code):
Sub Macro_Add_Residents()
Dim rVillaNo As Range
Dim resName As String
Dim Resp As String
Dim Meal As VbMsgBoxResult
Dim GFO As VbMsgBoxResult
Dim More As String
Sheets("residents").Select
Range("j1").Select
Do
Resp = InputBox("What is their Villa Number?")
' stop triggering other macro(s)
Application.EnableEvents = False
If Len(Resp) > 0 Then
On Error Resume Next
Set rVillaNo = Range("VillaNo").Find(What:=Resp, LookAt:=xlWhole, SearchDirection:=xlDown)
If Not rVillaNo Is Nothing Then
Do
Application.Goto Reference:=rVillaNo.Offset(, -3), Scroll:=True
' collect one resiident's name
resName = rVillaNo.Offset(n, -2) & " " & rVillaNo.Offset(n, -1)
' see if they want a meal
Meal = MsgBox("Is " & resName & " coming?", vbYesNo, "Add this Resident to List " & Resp)
' act on response
Select Case Meal
Case vbYes
'if Yes, set value
rVillaNo.Offset(n, -3).Value = 1
GFO = MsgBox("Gluten Free for " & resName & "?", vbYesNo, "Gluten Free Meal " & Resp)
' chck if this resident needs GF
If GFO = vbYes Then
rVillaNo.Offset(n, 2).Value = "Y"
Else
rVillaNo.Offset(n, 2).Value = ""
End If
Case vbNo
'if No, clear value and payment cells
'rVillaNo.Offset(n, -3).Value = ""
' rVillaNo.Offset(n, 4).Resize(1, 2).Interior.Color = 16777215
'rVillaNo.Offset(n, 2).Value = ""
Exit Sub
Case Else
Exit Sub
End Select
' increment a counter
n = n + 1
' go back if there's another resident in that villa
Loop Until rVillaNo.Offset(n, 0).Value <> rVillaNo.Value
End If
End If
More = MsgBox(" Add more Residents to the List", vbYesNo, "Continue to add Residents")
Select Case More
Case vbYes
Loop
Case vbNo
Exit Sub
End If
End Select
Application.EnableEvents = True
End Sub
Last edited by a moderator: