Userform and Excel Intersecting values

call_me_sol · External Since: Mar 03, 2007 Posts: 14

Hi -- me again.

This time, I want to create a Userform with Comboxes and
docvariables. Here's the tricky part.

I want the Userform to populate the ComboBox with values from an
Excel worksheet. Let me give you an example:

Excel Table:
XXX YYY ZZZ
ABC 5 10 15
DEF 2 5 20
GHI 4 4 5

ComboBox1 will be populated with the headers XXX YYY ZZZ from the top
horizontal row
ComboBox2 will be populated with the headers ABC DEF GHI from the
vertical column

If user selects XXX from ComboBox1 and DEF from ComboBox2, the
resulting Userform spits out value "10" to a docvariable placed
somewhere in the document.

Is this possible?

Thanks again to anyone that replies to this post

Jean-Guy Marcil · External Since: Nov 30, 2006 Posts: 68

call_me_sol.DeleteThis@yahoo.com was telling us:
call_me_sol.DeleteThis@yahoo.com nous racontait que :

> Hi -- me again.
>
> This time, I want to create a Userform with Comboxes and
> docvariables. Here's the tricky part.
>
> I want the Userform to populate the ComboBox with values from an
> Excel worksheet. Let me give you an example:
>
> Excel Table:
> XXX YYY ZZZ
> ABC 5 10 15
> DEF 2 5 20
> GHI 4 4 5
>
> ComboBox1 will be populated with the headers XXX YYY ZZZ from the top
> horizontal row
> ComboBox2 will be populated with the headers ABC DEF GHI from the
> vertical column
>
> If user selects XXX from ComboBox1 and DEF from ComboBox2, the
> resulting Userform spits out value "10" to a docvariable placed
> somewhere in the document.
>
> Is this possible?

Yes, certainly.

What part exactly are you having difficulty with?

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE.DeleteThis@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

call_me_sol · External Since: Mar 03, 2007 Posts: 14

Well, actually, all of it Smile

Sorry, but I'm really new VBA.

So, my specific questions:

1) How do I make the userform look into the Excel file?
2) How do I make the userform auto-populate the two required
comboboxes with the table headers (xxx, yyy, zzz and abc, def, ghi)
3) How do I make the selection of those two comboboxes return the
resulting intersecting value (i.e., if XXX and ABC is selected, then
the value "5" is returned)
4) Finally, how do I take the value of "5" and turn it into a
variable so I can stick it in my document as a doc variable?

Thanks a lot!!!

On May 7, 10:26 pm, "Jean-Guy Marcil" <DontEvenTry@NoSpam> wrote:
> call_me_... DeleteThis @yahoo.com was telling us:
> call_me_... DeleteThis @yahoo.com nous racontait que :
>
>
>
> > Hi -- me again.
>
> > This time, I want to create a Userform with Comboxes and
> > docvariables. Here's the tricky part.
>
> > I want the Userform to populate the ComboBox with values from an
> > Excel worksheet. Let me give you an example:
>
> > Excel Table:
> > XXX YYY ZZZ
> > ABC 5 10 15
> > DEF 2 5 20
> > GHI 4 4 5
>
> > ComboBox1 will be populated with the headers XXX YYY ZZZ from the top
> > horizontal row
> > ComboBox2 will be populated with the headers ABC DEF GHI from the
> > vertical column
>
> > If user selects XXX from ComboBox1 and DEF from ComboBox2, the
> > resulting Userform spits out value "10" to a docvariable placed
> > somewhere in the document.
>
> > Is this possible?
>
> Yes, certainly.
>
> What part exactly are you having difficulty with?
>
> --
>
> Salut!
> _______________________________________
> Jean-Guy Marcil - Word MVP
> jmarcilREM... DeleteThis @CAPSsympatico.caTHISTOO
> Word MVP site:http://www.word.mvps.org

Jean-Guy Marcil · External Since: Nov 30, 2006 Posts: 68

call_me_sol.RemoveThis@yahoo.com was telling us:
call_me_sol.RemoveThis@yahoo.com nous racontait que :

> Well, actually, all of it Smile

>
> Sorry, but I'm really new VBA.
>
> So, my specific questions:
>
> 1) How do I make the userform look into the Excel file?
> 2) How do I make the userform auto-populate the two required
> comboboxes with the table headers (xxx, yyy, zzz and abc, def, ghi)

For point 1)and 2), see:
http://word.mvps.org/faqs/interdev/controlxlfromword.htm
http://word.mvps.org/faqs/interdev/FillListBoxFromXLDAO.htm
or
http://word.mvps.org/faqs/interdev/XLToWordWithDAO.htm
but you will need to tweak it to get a row instead of a column.

> 3) How do I make the selection of those two comboboxes return the
> resulting intersecting value (i.e., if XXX and ABC is selected, then
> the value "5" is returned)

Before you get to point 1) and 2) see:
http://word.mvps.org/faqs/Userforms/index.htm
in particular
http://word.mvps.org/faqs/Userforms/CreateAUserForm.htm

Then you can try to tackle point 3).
Basically you will need to get the ListIndex value of each of the two
comboboxes and translate that in an Excel cell address:
e.g.: the third item in the first combobox will be the fourth row, or 4
(because of the heading we need to add one), the second choice in the other
combobox will be the third column, or C (again, +1 because of the heading).
So the cell is C4, or R4C3.

> 4) Finally, how do I take the value of "5" and turn it into a
> variable so I can stick it in my document as a doc variable?

ActiveDocument.Variables("NAMEofVARIABLE").Value = "Value from C4 in Exel"

If you are a beginner in VBA, you should know that this is an advanced
project.
I suggest you get someone to do it for you, or, if this is not an option,
make sure you have plenty of time ahead of you!

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE.RemoveThis@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

call_me_sol · External Since: Mar 03, 2007 Posts: 14

Hi Jean-Guy -

I appreciate the difficulity of doing this, but I'm up for the
challenge. I have done a few userforms before (variables,
docvariables, listboxes, etc). But, dipping into excel and
manipulating the data I have not. I'm willing to learn though Smile

OK, a few questions on the URLs that you provided above:

When I define a range in Excel, does the range have to be contiguous
or can it be scattered cells around the page?
Can I define more than one named range or can there only be one
recordset per form?
If I cannot define more than one range, what's the best way to stick
half of the results in one Listbox and the other half in a different
ListBox?

Thanks!

Jean-Guy Marcil · External Since: Nov 30, 2006 Posts: 68

call_me_sol.DeleteThis@yahoo.com was telling us:
call_me_sol.DeleteThis@yahoo.com nous racontait que :

> Hi Jean-Guy -
>
> I appreciate the difficulity of doing this, but I'm up for the
> challenge. I have done a few userforms before (variables,
> docvariables, listboxes, etc). But, dipping into excel and
> manipulating the data I have not. I'm willing to learn though Smile

>
> OK, a few questions on the URLs that you provided above:
>
> When I define a range in Excel, does the range have to be contiguous
> or can it be scattered cells around the page?

I have never tried with non-contiguous ranges for this... You can try and
let us know what happens.
But I suspect that you will run into difficulties.

> Can I define more than one named range or can there only be one
> recordset per form?

I think you can have as many as you want. I have always worked with only
one, but I don't see why you couldn't do something like:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")

Set rs1 = db.OpenRecordset("SELECT * FROM `myDatabase1`")
Set rs2 = db.OpenRecordset("SELECT * FROM `myDatabase2`")
Set rs3 = db.OpenRecordset("SELECT * FROM `myDatabase3`")

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE.DeleteThis@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

call_me_sol · External Since: Mar 03, 2007 Posts: 14

Why can't I import ROWS?
I can import named columns, but I cannot import named rows?

I've tried the top most row (which I believe is referred to as the
Header row). I've tried random rows within the excel sheet. I simply
cannot import Rows. I feel like there's some special Row import
command that I'm missing but despite searching the internet for a
while I can't find anything. Anyone have any thoughts?

Jean-Guy Marcil · External Since: Nov 30, 2006 Posts: 68

call_me_sol.RemoveThis@yahoo.com was telling us:
call_me_sol.RemoveThis@yahoo.com nous racontait que :

> Why can't I import ROWS?
> I can import named columns, but I cannot import named rows?
>
> I've tried the top most row (which I believe is referred to as the
> Header row). I've tried random rows within the excel sheet. I simply
> cannot import Rows. I feel like there's some special Row import
> command that I'm missing but despite searching the internet for a
> while I can't find anything. Anyone have any thoughts?

Because when you retrieve records to populate a listbox or a combobox, rows
represents records and columns represent fields. All databases are like
that.

If you wan to retrieve rows, you are going to need to use something
different, for example (without testing any of this, just as suggestion):

For i = 1 To rs.Fields.Count
myCombobox.AddItem rs.Fields(i).Value
Next

This should get the first row.
Of course, if you want a row further down, you will need code to get to that
row first, and you will need code to make sure you don't try to access a row
(a record) number that is higher than the amount of rows in the named
range...

Alternatively, you can also use the Excel object model to get to all the
cells in the first row of a named range, in that case you will need to
actually open the Excel worksheet.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE.RemoveThis@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org