Hi guys,

I would like, to have some help for the beginning of my code.

I have a big spreadsheet with lot of data
I have a column which is filled with different words: supplier 1, Supplier 2, Customer.

I would like to write a code saying :

Choose supplier 1,or Supplier 2, or Customer.
then open a new workbook, and export data regarding the chosen word.

I would like help only for choosing the word and then openning a new workbook.

Thank you for your help.

Hi Jerry,

It sounds like you need a UserForm, since you're looking for the user to "choose" from a list. If so, step one would be to create a UserForm with a ComboBox (ComboBox1) and a Command Button (CommandButton1). After adding these items (and making the form look nice in general), add this code to the UserForm's module:

------------

Private Sub CommandButton1_Click()
sChosenWord = ComboBox1.Text
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim v As Variant

With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each v In Range("A1:A10") 'Change to suit
If Not IsEmpty(v.Value) And Not .exists(v.Value) Then
.Add v, Nothing
Me.ComboBox1.AddItem v
End If
Next v
End With

End Sub

-------------------

Next, in Module1, add this code:

--------------

Public sChosenWord As String

Sub OpenWorkbook()
Dim wbNew As Workbook

sChosenWord = vbNullString

UserForm1.Show

If sChosenWord = "" Then Exit Sub 'User canceled, exit

Set wbNew = Workbooks.Add

MsgBox "User chose: " & sChosenWord
End Sub


-----------------


To use this, just run the OpenWorkbook macro. It will call up the UserForm and display a unique list of values from the range you specify (in the example, A1:A10). Once the UserForm unloads, the macro checks for a null string and cancels if one is found. Otherwise, a new workbook is opened and a message box displays the value selected by the user.

Hope this helps get you started,

Ben

Cheers for your answer. You helped me a lot.

Anyway, I have more details of what I need. I ve done something but it is not very elegant.

Still is my big workbook.

I have two spreadsheets: one is "HOME" the other one is "ICD"

in the spreadsheet HOME I need a combo box where I can choose : suplier 1 or supplier 2 or Customer.

and a button export.

the code will have to export in the spreadsheet "ICD" the line where it is written the choosen word.


Could you give me an example of code saying this so I can modify mine ?

Cheers

Sorry I wanted to say :

the code will have to open a new workbook and export in it the line (in the sheet "ICD") where it is written the choosen word.

Jerry,

The macros below should do the trick. All you'll need to do is set up a drop-down list in any cell on your "HOME" sheet (using Data Validation). In a module of your workbook, add the code below and be sure to point "sParameter" to the correct cell in your workbook (this macro assumes that the drop-down list is in cell A1). Then add a button to the sheet and assign it the macro "CopyToNew".

Hope this helps,

Ben

CODE:
------------------------------
Option Explicit
Public sParameter As String
Public wbNew As Workbook

Sub CopyToNew()
'Set sParameter range to the range containing your drop-down list
sParameter = ThisWorkbook.Sheets("HOME").Range("A1").Value

Set wbNew = Workbooks.Add 'Adds a new workbook

'Copy header row to new sheet(assumes headers in row 1 of "ICD" sheet)
ThisWorkbook.Sheets("ICD").Rows(1).Copy wbNew.Sheets(1).Range("A1")

'Call macro to move the matching row(s)
MoveSheet wbNew.Sheets(1).Range("A2")

End Sub

Sub MoveSheet(rCopy As Range)
'Requires reference to ActiveX Data Objects Libraray
Dim sSQL As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strFile As String
Dim strCon As String

'Create recordset using SQL string
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

sSQL = "SELECT * FROM [ICD$] WHERE [" & ThisWorkbook.Sheets("ICD").Range("A1").Value & "] = " & _
Chr(39) & sParameter & Chr(39) & ";"

rs.Open sSQL, cn

'Copy Records to the new workbook
rCopy.CopyFromRecordset rs

'Close connection and exit
cn.Close
Set rCopy = Nothing
Set wbNew = Nothing

End Sub

hi Ben thank you for your help.
however it says that it doesn t recognize "Dim cn As ADODB.Connection"

and it highlights "Sub MoveSheet(rCopy As Range)" in yellow
what is the problem with that ?