Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate values in textboxes based on data sheet

0

hi 

I  try  populate  the  data  in multiple  textboxes   based on  condition.

may be  explenation  is  difficult   but  I  attach  picture  inside  sheet  and  fill  manually how  the  data  should  show  when  run  the  userform    . it  should  show  all  of  data  contain  minus  values for  all  of textboxes . each three textboxes contain  data columns A,B,C . just  see  the  picture  will understand  what I want 

Private Sub UserForm_Initialize()

Dim lr, x
lr = Sheets("DATA").Range("A" & Rows.Count).End(3).Row
With Sheets("DATA")
For x = 2 To lr
If .Cells(x, 3).Value < 0 Then
For i = 1 To 3
Me.Controls("textbox" & i).Value = Array(.Cells(x, 1), .Cells(x, 2), .Cells(x, 3))
Next
End If
Next
End With
End Sub
Answer
Discuss

Answers

0
Selected Answer

Ali

You only have 5 rows or text boxes (for negative) so I've added a variable called Ctrl (for the text box row) so it stops looking if too many negative numbers are found. I changed the code in the attached revised file as follows (with changes shown in bold, plus indenting for easier code reading):

Private Sub UserForm_Initialize()

Dim lr As Long, x As Long, Ctrl As Long

lr = Sheets("DATA").Range("A" & Rows.Count).End(3).Row
Ctrl = 0

With Sheets("DATA")
    For x = 2 To lr
        If .Cells(x, 3).Value < 0 Then
            For i = 1 To 3
                Me.Controls("textbox" & Ctrl * 3 + i).Value = .Cells(x, i)
            Next i
            Ctrl = Ctrl + 1
        End If
        If Ctrl = 5 Then Exit For
    Next x
End With

End Sub

Note that in the Userform, I've displayed the Item number from the data  but you could use Ctrl to set that column (as 1 to 5) of TextBoxes instead.

There's now also a button on the sheet to click and call this sheet code to display the form:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Hope this makes sense and solves your problem.

Discuss

Discussion

Hi John,
many  thanks   for  your help .
Ali M (rep: 28) May 23, '22 at 6:39 pm
Thanks for selecting my answer, Ali.

I think your use of 5 rows textboxes could cause problems, especially if you are using the form for replacing stock say (where you wouldn't get the 6th negative number etc.).

Suggest you look at using a Listbox or a ComboBox instead (where that limit need not apply). Look in the Tutorials section where I think Don has a lesson on that.
John_Ru (rep: 6092) May 24, '22 at 2:34 am
Suggest you look at using a Listbox or a ComboBox instead 
I expected to  you say that .
to  clarification why  I  use  textboxes , because  I  want  making  userform as messagebox. the  messagebox can't  change  font size,bold ....etc  and  also  limited character, listbox doesn't allow  the  highlight some values or items , but  when  create textboxes or lables on  userform  I can  add many  items than more  the messagebox  and I  can  change formatting as what I want . thanks for  your  time
Ali M (rep: 28) May 24, '22 at 6:53 am
Understood Ali. I believe you can modify ListBox formatting but it's complex (and I'm only an amateur) so your approach seems best. Glad I've been able to help you reach your goal.
John_Ru (rep: 6092) May 24, '22 at 7:49 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login