Thank you, TOM OGLIVY: linking across the worksheets

[mike]

I have created a user form, actually been toying with the
idea of a dialog box as well. anyways, the form is
referred to the PlantForm_192. say i have 23 areas that
are to be landscape over a long time frame. My intentions
are to produce some sort of user form where they can
choose the area that is active that day, choose from
plant lists that is being planted, and how many.**I
already have a substantial lists of each in a individual
worksheet called MasterList_192**
my question is..how do i link the form or data or the
down lists in the form, once selected by user...to go and
search the wrkshts and fill in the data where needed?
Example..
user picked from drop down lists: Area 15, Plant1- 10
items,Plant2 - 25 items, Area 12..Plant3 - 50 items.
Result: FInds wrksheet with Area 15 and puts value of 10
into cell associated to Plant 1 & 25 for Plant 2. then
finds wrksht for Area 12 and inputs 50 for Plant 3.

Is this a macro statement i need to create or is there a
FIND or a function...been tinkering all over the help
section for the best effective way of doing this. I have
been playing w/macros so i would be open to macros.
THANKS!

 

[Tom Ogilvy]

I definitely think this would be a macro approach.

The follow psuedo code represents an approach with a multicolumn,
multiselect listbox (assuming the user chooses unique combinations of Area,
Plant and Qty) If these are separate choices or entries in textboxes, then
the code would be somewhat different. Assumes sheets to be search are named
Data1, Data2, Data3.

varr = Array("Data1","Data2","Data3")
With Userform1.Listbox1
for i = 0 to .Listcount -1
if .Selected(i) = True then
sArea = .list(i,0)
sPlant = .List(i,1)
sQty = .List(i,2)

for each sh in Worksheets(varr)
set rng = sh.Cells.Find(sArea)
if not rng is nothing then
set rng1 = sh.Cells.Find(sPlant)
if not rng1 is nothing then
rng1.Offset(0,1).Value = sQty
exit for
end if
end if
Next sh
End With

 

[Guest]

Again thanks Tom...this excel and macro world is making
my life soo easy..
Tom.. reading yur response. sounds great. Love the idea,
i wasnt aware that i can use a multicolumn , multi select
listbox. Is that the control "combo box " or "Listbox"
and i set it to be used as a multi? Heres what i had...i
had it as a listbox for each one individually. How owuld
i change it to be one list box with multicolumn...?


The choices were being picked by the users in each cell.
as a list box control . okay..heres what i have in real
terms. on the user form or dialog box(are they the same
thing?), i have each asking a question in text then a
drop down list box where they can select their choice;intersections(areas), Plants used..Drop down box w/all
plant available..etc for the qnty planted. ( would this
be better if done on a worksheet and just have the
controls on it directly and create it as a template and
protect it?) the areas are defined as wrksheets labeled
by their intersection name...IE: Independence Int, Brand
Int, Custer Int,...

 

[Tom Ogilvy]

A userform is a dialog - you can also build dialogs with dialogsheets, but
those are mostly applicable to older versions of Excel.

Based on the description, I think I would stick with the independent
choices. Whether you do it on a worksheet or with a userform would be up to
you. A worksheet might be easiest, but I have no personal knowledge of what
you are trying to do, who your users are and so forth.

If the sheets represent the area, then you would just go to that sheet to
search for the plant. Assumes single column, single select listboxes.

With Userform1
With .Listbox1
sArea = .List(.ListIndex)
End With
with .Listbox2
sPlant = .List(.ListIndex)
end with
sQty = .Textbox1.Text
End With
set sh = Worksheets(sArea0
set rng1 = sh.Cells.Find(sPlant)
if not rng1 is nothing then
rng1.Offset(0,1).Value = sQty
end if

 

[Guest]

i agree tom,

however i love the idea of the multi select and column
listbox... i am willing to arrange the form to make it
easier for the users.

a little more description... hope you can help...

* the users will be me and a select few of employees who
are well informed of the excel app.*
ie: I drive out to an area (say Brand Int) i watched a
crew install 50 live oaks and 35 Yucca plants.
then drive to next intersection at Independence Int...25
Red oaks and 2000 blueStem grass.

Can i do a listbox for the area and then for each area
(intersection) to have a listbox or combo box??

Can you get a combo box to put out both columns of data
or however many there is according to range defined? by
using the boundcolumn value..noticed you can only out one
number right?
What do you suggest for this scenario??

i want something for them to select area worked and then
select plant /qnty and have it dispersed to appropriate
area(intersection) wksht.
thanks again fo ryour time..its the first time for me to
actually confer back and forth on here..usually lost in
posting or never responded by the helper.

 

[mike]

oh tom,,, forgive me...

more info..

on the "MasterPlantList_192" (wksht) i have 3 columns..
Item #, Plant description, BidCost
on the wksht individualy named for their intersections..

I have column A as item#, B as Description, C as cost, D
is Unit (of measure), E is Section (total qnty on plan),
F is Area (actual qnty of area being worked) then from G
thru al..I have it set up as a 30-day columns, where i
can input how much was planted each day.;
just realized something else..i would have to have this
userform stored to sheets by current date of form.

i hope this helped a bit more.
How do i send a samlpe of my workbk or sheets toyou for
yu to look at?? and offer a suggestion?
MIke

 

[Guest]

man my head is popping out questions!.
i forget to ask..where is the multicolumn, multiselect
listbox you mentioned in your first reply??
is this a listbox with apprpriate values selected??
~Im stumped"

 

[Tom Ogilvy]

You can't really have a multiselect combobox and as you state, a combobox
can only return the selection for one column although it can display
multiple columns in the dropdown.

I don't see the utility of having choices like

Area1 Yucca
Area1 Pine
Area1 Spruce
Area1 Redwood
Area2 Yucca
Area2 Pine
Area2 Spruce
Area2 Redwood
Area3 Yucca
Area3 Pine
Area3 Spruce
Area3 Redwood
etc.

You could write code that has a multiselect single column listbox that
allows you to select multiple areas, then have comboboxes (or listboxes) for
each area that becomes "live" when that area is selected. That would
require the areas to be finite and static I would think unless you wanted to
have code to create the comboboxes.

But it sounds like these would need to be multiselect listboxes as well
since you watched multiple plants being emplaced - but then the challenge is
entering the quantity for each.

When you settle on what you are going to do, it might be appropriate to
consider writing code to implement it.

 

[mike]

Yes tom,

I can see now what you mean..

Each listbox can be multi-column and in each one you can
select mulitple choices...that i just now understood.
wow..okay
well i think i would go with this idea.

The wrksheets will be named by intersection(areas).
a user form to pop up when an icon is selected from the
worksht that is available when the workbook is opened.(
how do i do that); on the userform.?
**they should come up with a awesome listbox that will
allow users to select unique choices across each column
for however amny there is. And then have a code sort it
out to its linked cells and make list from it** would be
cool huh?***

Anyways, i think i will go with a single column listbox
for each selection and have each list box with procedure
to send the selected choice to its cell across the
worksheets.
Like: Listbox1 will be area...and only one choice can be
selected.
Listbox2 will be plantlist which multiple choices can be
selected.
I guess make a seperate macro to read the choices and
list it in a seperate listbox and then provide a text box
for user to input qnty for each one. From all that to
send the data to approp. cells across the workbook.

Does this sound more feasible? acceptable?
damn i love this vb stuff , only wished i learned it long
ago!

TOM, thanks agian for your patience and help.