×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA UserForms in Excel

VBA UserForms in Excel

VBA UserForms in Excel

(OP)
Hi

I would like some help wit a UserForm that I am about to create. The UserForm is suppose to present a list for the user in a ListBox and the user is then to select one row. Depending on the selection, data will be transferred from Sheet3 to Sheet1 in my excel document. In my user form I have to buttons, one OK button and one Cancel. The OK will make the transfer and the Cancel will cancel the UserForm. Below you can se my coda so far. I can read in data to my ListBox from Sheet3 but how am I suppose to get the selected data to sheet one. As you might notice I am totally new to using VBA in excel.

-----------------------------------------------------------
Private Sub CommandButton1_Click()

End Sub
-----------------------------------------------------------
Private Sub CommandButton2_Click()

End Sub
-----------------------------------------------------------
Private Sub UserForm_Initialize()
      Dim Lrange As Range
      Dim Larray() As Variant
      Dim x As Variant
      Dim ctr As Integer

      'Set the range to loop through
      Set Lrange = Sheet3.Range("A2:A53")

      'Loops through the ranges
      For Each x In Lrange

      ReDim Preserve Larray(ctr)

          'Add an item to the array
          Larray(ctr) = x.Value

          ctr = ctr + 1

      Next x

      'Assign the array to the listbox
      ListBox1.List = Larray

End Sub
-----------------------------------------------------------

RE: VBA UserForms in Excel

At the moment the clicking of the buttons doesnt run any code. You need some code in the sub commandbutton1_click(), something like

Sheets("sheet1").Cells(8, 1).Value = ListBox1.Value

RE: VBA UserForms in Excel

(OP)
Thanks I managed to do just that just before you’re reply. As a follow up question I am wondering if it is possible to write the value from another sell in into Cell(8,2).

In my case the data in ListBox1 comes from colum 1 in Sheet3
Can I with the code from above get the data from colum 2 (and same row as ListBox1) in Sheet3 in to cell(8,1)

RE: VBA UserForms in Excel

Firstly you need to find which row the result of your listbox is in.

If the data in the listbox is the same order as the data on your sheet you can use the listindex property. This would return 0 if the first item in the list is selected, 1 if the second item is selected and so on. Then a line of code similar to that used earlier to write the value to the cell

The code would be something like below

row_number = ListBox1.ListIndex
sheets("sheet1").cells(8,1).value = sheets("sheet2").cells(row_number,2).value

Remember this will only work if the order of the data in the list box is the same as the original sheet.

RE: VBA UserForms in Excel

correction to above post

As the listindex returns 0 for item at top of list but your original data only starts at row 2, the code should read

row_number = ListBox1.ListIndex + 2

RE: VBA UserForms in Excel

(OP)
Thanks for you’re support problem solved! =)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources