Home > Blockchain >  How to insert values into sheet from a UserForm in VBA
How to insert values into sheet from a UserForm in VBA

Time:11-22

This is my first time creating a form so very new to it.

I have the following form:

Private Sub UserForm_Activate()
'add all options for the different funds
With Me.ComboBox1
    .Clear
    .AddItem "Main Fund"
    .AddItem "Quant Fund"
End With

With Me.ComboBox2
    .Clear
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"
    .AddItem "April"
    .AddItem "May"
    .AddItem "June"
    .AddItem "July"
    .AddItem "August"
    .AddItem "September"
    .AddItem "October"
    .AddItem "November"
    .AddItem "December"
End With

With Me.ComboBox3
    .Clear
    .AddItem "PnL"
    .AddItem "Number of employees"
    .AddItem "Number of positions"
End With

End Sub

Which looks like:

enter image description here

The usage of the form is:

  1. A user in the worksheet, selects a cell
  2. The user call the CallForm subroutine and the form appears
  3. The user inputs the three arguments and the corresponding data from the following excel sheet is inserted int the selected cell:

enter image description here

Sub CallForm()
    MyForm.Show
End Sub

Please let me know if any clarification is needed or if i've missed out important information, any help would be fab since i've tried googling but really need help.

CodePudding user response:

You use a range in the sheet and set it to the value in your userform under the subroutine for pushing the "insert" button, e.g.:

Sub commandbutton1_click()
    With sheets("nameOfSheet")
        .cells(7,15).value = Me.Combobox1.text
    End with
End sub

All of the standard ways of finding your range apply, e.g., Find() or Match().

  • Related