Change label's caption with loop in a VBA userForm

Question

I want my code to loop through a range of cells and every time show the content of the cell (that is a question) in a label's caption of a userForm and allow the user to choose the button of "Yes" (for knowing the answer) or "NO" (for not knowing the answer) and then the code use the user's response to perform some actions and then continue the loop for other cells.

For each iteration in for loop i need user be allowed to choose "Yes" or "No" or "Cancel" and then the userForms code continue running.

Here is some of the code:

Private Sub UserForm_Activate()

For i = 2 To n
Cells(i, 3) = Cells(i, 3) + 1 
If Cells(i, 3) = 1 Then 
    UserForm1.Controls("question").Caption = Cells(i, 1).Value 'lable 1 
    UserForm1.Controls("answer").Caption = ""                  'lable 2               
    'some codes...
elseIf Cells(i, 3) = 3 Then
UserForm1.Controls("question").Caption = Cells(i, 1).Value 'lable 1 
UserForm1.Controls("answer").Caption = ""                  'lable 2  
next
end sub

and i need to run these codes whenever user clicks on a button on the form . then the rest of the above code be executed .

Private Sub ansYes_Click() 'If user clicks Yes show the answer and continue
  UserForm1.Controls("answer").Caption = Cells(i, 2)
  UserForm1.Controls("answer").Visible = True
End Sub
Private Sub ansNo_Click()  'If user clicks No show the answer and continue
  UserForm1.Controls("answer").Caption = Cells(i, 2)
  UserForm1.Controls("answer").Visible = True
Cells(i, 3) = 0
Cells(i, 4) = 1
End Sub
Private Sub ansCancel_Click() 'If user clicks cancel unload userform and exit the for loop
  Unload UserForm1
End Sub

Can you show some more of your code? I think a simple solution might be to let user answer in a separate MsgBox, and tying further actions to the result of the MsgBox. — riskypenguin
– riskypenguin, Commented Oct 2, 2019 at 8:22
You can effectively pause the application until the user clicks a button by setting the form to display Modal in the properties window. — ProfoundlyOblivious
– ProfoundlyOblivious, Commented Oct 2, 2019 at 9:28
@M.Schalk yes i have added codes to the question . I have used msgbox in another code and it runs perfectly but i want to use userForm so i can change the font size that is shown for user — km-b
– km-b, Commented Oct 2, 2019 at 12:25
Try setting ShowModal of your UserForm to True, as ProfoundlyOblivious suggested. — riskypenguin
– riskypenguin, Commented Oct 2, 2019 at 12:27

ProfoundlyOblivious · Accepted Answer · 2019-10-05 18:35:06Z

You haven't shown us where you show the UserForm or how it is defined within your project and you haven't given a good description of the problem. This forces assumptions that may not be accurate.

Do not name your UserForm "UserForm1", give it a useful name. Henceforth, I will refer to this as "UserResponseForm"

Do not use default instance of a UserForm, use a variable and learn the difference between Dim As and Dim As New. The difference is significant and important.

Dim myForm As UserResponseForm

Dim myForm As New UserResponseForm

Using UserResponseForm's property window, find and rename your labels "Question" and "Answer"

In UserResponseForm's code use Ctrl + H to replace:

UserForm1.Controls("answer") with Me.Answer

and

Unload UserForm1 with Me.Hide

Move all of your code from Private Sub UserForm_Activate to a normal module. Give the module and the sub a useful name. I will refer to module as "QuestionaireCodeModule" and the sub as "RunTheQuestionnaire"

The QuestionaireCodeModule should look similar to this

Option Explicit

Private Const n As Long = 20 'you didn't show how n was populated or scoped so I put it here

Sub RunTheQuestionaire()

    Dim i As Long
    Dim myForm As New UserResponseForm 

    For i = 2 To n

        Cells(i, 3) = Cells(i, 3) + 1 

        If Cells(i, 3) = 1 Or Cells(i, 3) = 3 Then 'added or condition for clarity
            myForm.Question.Caption = Cells(i, 1).Value 'lable 1 
            myForm.Answer.Caption = vbNullString                 'lable 2        

            'some codes...

            'next three lines removed because they are redundant
        'elseIf Cells(i, 3) = 3 Then
            'UserForm1.Controls("question").Caption = Cells(i, 1).Value 'lable 1 
            'UserForm1.Controls("answer").Caption = ""                  'lable 2  

        End If

        myForm.Show
        If Not myForm.Visible Then Exit For

    Next i

End Sub

That should be it but I can't say if this will work for you or not because you did not provide enough to work with and the code you provided in your example won't compile.

Collectives™ on Stack Overflow

Change label's caption with loop in a VBA userForm

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related