baby_brown2

New Member
Joined
Nov 8, 2010
Messages
2
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!!!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

New Member
Joined
Nov 8, 2010
Messages
2
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,454
Messages
5,601,764
Members
414,471
Latest member
cchunyadi

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
Top