User form help!

[baby_brown2]

Hello everyone,

I am currently building a data entry spreadsheet and have succesfully added a user form created in VB.

I have several list boxes in the user form.

Basically - I was wondering if it was possible to do the following.

When a selection is made in the list box, it will then look something up in another work book which will define where the information placed in the data form will be sent?

Sorry - I don't know if that makes much sense.

For example, in a userform, If I have a list box with John, Jack, Pete and Paul in and another list below with Transfer, Process and Service in... If Jack is selected from the first list and Process is selected from the second, it will then look into a specific cell (say D4) in another workbook to see if there is a 1 or a 0 in that cell. If there is a 1, the information input will go to sheet 1, if it's a 0 it will go to sheet 2?

Any help would be very much appreicated!!!

 

[Richard Schollar]

Hi

You probably would want to have the check done when say a Submit button was pressed, so you might want to plonk a command button for this purpose on the userform. Then, in the click event of this command button you can check the listbox values and this other workbook's values:

Private Sub SubmitButton_Click()
 
With Me
  If .ListBox1.Value = "Jack" Then
     if .ListBox2.Value = "Process" Then
       If Workbooks("SomeOtherWorkbook").Sheets("SomeOtherReferenceSheet").Range("D4").Value = 1 Then
           Sheets("Sheet1").Range("A1").Value = .ListBox1.Value
           'etc etc
       Elseif Workbooks("SomeOtherWorkbook").Sheets("SomeOtherReferenceSheet").Range("D4").Value = 0 Then
            Sheets("Sheet2").Range("A1").Value = .ListBox1.Value
           'etc etc
        end if
    end if
  end if
End with 
End Sub

Note that I'm not suggesting you do multiple nested ifs like this (perhaps use a Select Case) but it demonstrates how you could do what you asked.

 

[baby_brown2]

Thanks for the reply!

I am still really confused - I used to know codes etc but haven't used them for a very long time so have had to use this site a lot to build my spreadsheet!

Someotherwork sheet - Would I put the whole destination in... For example... C:/.....etc etc?

Also - Would this make it find the next available free line?

Sorry to be a pain!

 

[Richard Schollar]

Is the other workbook already open in Excel or do you need to open it up? Is your intention to place the userform data into this other workbook? The code as it stands doesn't find the next available row - that would need to be factored in.