Excel user form required with vlook formula

ramebits

New Member
Joined
Aug 30, 2018
Messages
6
textbox value not appear in sheetname "Writeoff" - Suppose I English from combobox1 and Year 2021-21 from combobox2, I want only textbox vlalue appear in sheetname"Writeoff" in front of subect select for Row and in selected year column is

Combobox1 subjects are English, Hindi, Maths, Sociology whic are are in B2:B10 and combobox2 have 2019-20, 2020-21, 2021-22 etc. are in C1:R1.

Suppose I select English form combobox1 and 2021-21 from combobox2, only the value of textbox tranfer in sheetname "Writeoff" cell
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I assume you are trying something like in the following image:

1738699930704.png

Try the following code. It is important to load the combos as I have in the Activate event, that way we know in which row and in which column the selected data is in its respective combobox.
And therefore, it is not necessary to use the vlookup function.

VBA Code:
Private Sub CommandButton1_Click()
  Dim r As Long, c As Long
  
  If ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox2.ListIndex = -1 Then Exit Sub
  If TextBox1.Value = "" Then Exit Sub
  
  r = ComboBox1.ListIndex + 2
  c = ComboBox2.ListIndex + 3
  
  Sheets("Writeoff").Cells(r, c).Value = TextBox1.Value
  
End Sub

Private Sub UserForm_Activate()
  With Sheets("Writeoff")
    ComboBox1.List = .Range("B2", .Range("B" & Rows.Count).End(3)).Value
    ComboBox2.List = Application.Transpose(.Range("C1", .Cells(1, Columns.Count).End(1)).Value)
  End With
End Sub

🧙‍♂️
 
Upvote 0
Solution
Dim r As Long, c As Long If ComboBox1.ListIndex = -1 Then Exit Sub If ComboBox2.ListIndex = -1 Then Exit Sub If TextBox1.Value = "" Then Exit Sub r = ComboBox1.ListIndex + 2 c = ComboBox2.ListIndex + 3 Sheets("Writeoff").Cells(r, c).Value = TextBox1.Value End Sub Private Sub UserForm_Activate() With Sheets("Writeoff") ComboBox1.List = .Range("B2", .Range("B" & Rows.Count).End(3)).Value ComboBox2.List = Application.Transpose(.Range("C1", .Cells(1, Columns.Count).End(1)).Value) End With
Thanks Lot Its working
 
Upvote 0

Forum statistics

Threads
1,226,697
Messages
6,192,498
Members
453,727
Latest member
tuong_ng89

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