Userform Help looking up different properties

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,229
Office Version
  1. 2010
Platform
  1. Windows
Hello good afternoon, please can you help me i have a user form where i have a 'Textbox1' to enter a diameter, and a 'Textbox2' to enter length. please see data.jpg.

for Example if diameter is M3, M4, M5, M6 on Row 1 from 'sheet1', then it enters the results in the 'Userform1', for ds (Testbox7), s (Textbox1), e (Textbox13), k (Textbox15), dw (Textbox17), c (Textbox19) and r into (Textbox21)

Please can you helpme.
 

Attachments

  • data.jpg
    data.jpg
    232.6 KB · Views: 13
  • userform.jpg
    userform.jpg
    78.5 KB · Views: 13

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Patriot2879 said:
Hello good afternoon, please can you help me i have a user form where i have a 'Textbox1' to enter a diameter, and a 'Textbox2' to enter length. please see data.jpg.

for Example if diameter is M3, M4, M5, M6 on Row 1 from 'sheet1', then it enters the results in the 'Userform1', for ds (Testbox7), s (Textbox1), e (Textbox13), k (Textbox15), dw (Textbox17), c (Textbox19) and r into (Textbox21)

Please can you helpme.
Click to expand...
Good afternoon hope someone can help me on the above please? Thank you
 
Upvote 0
Patriot2879 said:
Good afternoon hope someone can help me on the above please? Thank you
Click to expand...
Hi i have managed to get some of it working now with this code below - but how do i get it to look at a range? for example in textbox2 a number is entered between 1 and 125 then it looks at sheet2 row C, but if the range entered is between 126 and 300 it looks at row D? please can you help with this please.

VBA Code: 
Private Sub TextBox1_AfterUpdate()
   Dim Fnd As Range
  
   Set Fnd = ThisWorkbook.Sheets("Diameter").Range("A:A").Find(TextBox1.Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      TextBox7.Value = Fnd.Offset(, 1).Value
      TextBox11.Value = Fnd.Offset(, 2).Value
      TextBox13.Value = Fnd.Offset(, 3).Value
      TextBox15.Value = Fnd.Offset(, 4).Value
      TextBox17.Value = Fnd.Offset(, 5).Value
      TextBox19.Value = Fnd.Offset(, 6).Value
      TextBox21.Value = Fnd.Offset(, 7).Value
   End If
  
End Sub
 
Upvote 0
Hi Patriot2879. Glad that you were able to sort most of this out without assistance. Lack of assistance usually means that your question isn't clear and members are not able to help. For example, there is no "row C" or "row D" .... columns yes. It's not real clear what you're trying to find OR what row(s) you want searched. Here's my guess. HTH. Dave
Code: 
Dim r As Range, Rng As Range
With Sheets("Sheet2")
If userform1.textbox2.Value < 125 Then
Set Rng = .Range(.Cells(1, "C"), .Cells(125, "C"))
Else
Set Rng = .Range(.Cells(126, "D"), .Cells(300, "D"))
End If
End With


For Each r In Rng
If r.Value = userform1.textbox2.Value Then
'do stuff
End If
Next r
 
Upvote 0
NdNoviceHlp said:
Hi Patriot2879. Glad that you were able to sort most of this out without assistance. Lack of assistance usually means that your question isn't clear and members are not able to help. For example, there is no "row C" or "row D" .... columns yes. It's not real clear what you're trying to find OR what row(s) you want searched. Here's my guess. HTH. Dave
Code: 
Dim r As Range, Rng As Range
With Sheets("Sheet2")
If userform1.textbox2.Value < 125 Then
Set Rng = .Range(.Cells(1, "C"), .Cells(125, "C"))
Else
Set Rng = .Range(.Cells(126, "D"), .Cells(300, "D"))
End If
End With


For Each r In Rng
If r.Value = userform1.textbox2.Value Then
'do stuff
End If
Next r
Click to expand...
Hi thank you for getting back to me, and helping me with the coding, i have 2 textboxes, textbox1 and textbox2.

Textbox1 is where they enter the size, ie M3, M4, M5 or M6 and so on, which is on line1 of sheet ("Length")
Textbox2 to is where they enter the length range from 0 - 1000, but depending on what number they enter is dependant on what line they look at.
for example if they enter M3 and 58 then the answer would be 12 which needs to be populated in Textbox5.
For example if they enter M5 and 157 then the answer would be 22 and poplulated into Textbox5.

Hope this makes sense and that you can help me please.
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    63.6 KB · Views: 2
Upvote 0
Not very eloquent or robust, but you can trial this...
Code: 
With Sheets("Length")
Select Case Userform1.textbox1.Text
Case "M3"
If Userform1.textbox2.Value < 125 Then
Userform1.textbox5.Value = .Range("C" & 2)
ElseIf Userform1.textbox2.Value > 125 And _
             Userform1.textbox2.Value < 200 Then
Userform1.textbox5.Value = .Range("C" & 3)
ElseIf Userform1.textbox2.Value > 200 And _
             Userform1.textbox2.Value < 1000 Then
Userform1.textbox5.Value = .Range("C" & 4)
End If
Case "M4"
If Userform1.textbox2.Value < 125 Then
Userform1.textbox5.Value = .Range("D" & 2)
ElseIf Userform1.textbox2.Value > 125 And _
             Userform1.textbox2.Value < 200 Then
Userform1.textbox5.Value = .Range("D" & 3)
ElseIf Userform1.textbox2.Value > 200 And _
             Userform1.textbox2.Value < 1000 Then
Userform1.textbox5.Value = .Range("D" & 4)
End If
'etc, etc for M5, M6

End Select
End With
Dave
 
Upvote 0
NdNoviceHlp said:
With Sheets("Length") Select Case Userform1.textbox1.Text Case "M3" If Userform1.textbox2.Value < 125 Then Userform1.textbox5.Value = .Range("C" & 2) ElseIf Userform1.textbox2.Value > 125 And _ Userform1.textbox2.Value < 200 Then Userform1.textbox5.Value = .Range("C" & 3) ElseIf Userform1.textbox2.Value > 200 And _ Userform1.textbox2.Value < 1000 Then Userform1.textbox5.Value = .Range("C" & 4) End If Case "M4" If Userform1.textbox2.Value < 125 Then Userform1.textbox5.Value = .Range("D" & 2) ElseIf Userform1.textbox2.Value > 125 And _ Userform1.textbox2.Value < 200 Then Userform1.textbox5.Value = .Range("D" & 3) ElseIf Userform1.textbox2.Value > 200 And _ Userform1.textbox2.Value < 1000 Then Userform1.textbox5.Value = .Range("D" & 4) End If 'etc, etc for M5, M6 End Select End With
Click to expand...
Hi Thankyou for the code, please can you advise where i place this please?
 
Upvote 0
That wasn't quite right...
Code: 
With Sheets("Length")
Select Case UserForm1.TextBox1.Text
Case "M3"
If UserForm1.TextBox2.Value <= 125 Then
UserForm1.TextBox5.Value = .Range("C" & 2)
ElseIf UserForm1.TextBox2.Value > 125 And _
             UserForm1.TextBox2.Value <= 200 Then
UserForm1.TextBox5.Value = .Range("C" & 3)
ElseIf UserForm1.TextBox2.Value > 200 And _
             UserForm1.TextBox2.Value <= 1000 Then
UserForm1.TextBox5.Value = .Range("C" & 4)
End If
Case "M4"
If UserForm1.TextBox2.Value <= 125 Then
UserForm1.TextBox5.Value = .Range("D" & 2)
ElseIf UserForm1.TextBox2.Value > 125 And _
             UserForm1.TextBox2.Value <= 200 Then
UserForm1.TextBox5.Value = .Range("D" & 3)
ElseIf UserForm1.TextBox2.Value > 200 And _
             UserForm1.TextBox2.Value <= 1000 Then
UserForm1.TextBox5.Value = .Range("D" & 4)
End If
'etc, etc for M5, M6

End Select
End With
This is userform code. Use a command button on the userform to operate. Dave
 
Upvote 0
NdNoviceHlp said:
That wasn't quite right...
Code: 
With Sheets("Length")
Select Case UserForm1.TextBox1.Text
Case "M3"
If UserForm1.TextBox2.Value <= 125 Then
UserForm1.TextBox5.Value = .Range("C" & 2)
ElseIf UserForm1.TextBox2.Value > 125 And _
             UserForm1.TextBox2.Value <= 200 Then
UserForm1.TextBox5.Value = .Range("C" & 3)
ElseIf UserForm1.TextBox2.Value > 200 And _
             UserForm1.TextBox2.Value <= 1000 Then
UserForm1.TextBox5.Value = .Range("C" & 4)
End If
Case "M4"
If UserForm1.TextBox2.Value <= 125 Then
UserForm1.TextBox5.Value = .Range("D" & 2)
ElseIf UserForm1.TextBox2.Value > 125 And _
             UserForm1.TextBox2.Value <= 200 Then
UserForm1.TextBox5.Value = .Range("D" & 3)
ElseIf UserForm1.TextBox2.Value > 200 And _
             UserForm1.TextBox2.Value <= 1000 Then
UserForm1.TextBox5.Value = .Range("D" & 4)
End If
'etc, etc for M5, M6

End Select
End With
This is userform code. Use a command button on the userform to operate. Dave
Click to expand...
That works great thank you, how do i adapt it to if the use lowercase or uppercase for the M3, M4 etc? ie m3, m4? thank you very much
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
USERFORM button for UPDATE error "block if without end if"
2
Replies
16
Views
610
dmt32
D
L
  • Question
Userform help , sending data to sheet
Replies
8
Views
435
learnig1
L
T
  • Solved
UserForm adding data from textbox and optionbutton to worksheet
Replies
9
Views
2K
Fluff
Fluff
D
  • Question
UserForm not updating properly
2 3
Replies
22
Views
2K
dmt32
D
E
  • Question
Calculate Userform Text box from other text boxes
Replies
4
Views
1K
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,274
Messages
6,123,991
Members
449,137
Latest member
abdahsankhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top