I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson

ps:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson

wonderful!~ Thanks so much I will try this tomorrow and let you know how it
goes. THanks again!
--
Thanks,,
Kim


"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim


"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim


"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

will do thanks!

I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

--
Thanks,,
Kim


"Dave Peterson" wrote:

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Merged cells are going to screw up the routine anyway. That macro is going to
try to put a value in each of those cells (depending on how many were selected).

I'd unmerge those cells.

DestCell.Resize(.ListCount, 1).value = ""
might work, but I think you'll still have trouble with the rest of the code.


Kim K wrote:

will do thanks!

I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

--
Thanks,,
Kim

"Dave Peterson" wrote:

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

That is interesting, however, where the listbox is and the macro button, no
cells are merged, but at the top of the page I have merged cells, is this the
cause?
--
Thanks,,
Kim


"Dave Peterson" wrote:

Merged cells are going to screw up the routine anyway. That macro is going to
try to put a value in each of those cells (depending on how many were selected).

I'd unmerge those cells.

DestCell.Resize(.ListCount, 1).value = ""
might work, but I think you'll still have trouble with the rest of the code.


Kim K wrote:

will do thanks!

I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

--
Thanks,,
Kim

"Dave Peterson" wrote:

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

The button puts the selected items into a cell, er, a bunch of cells.

I chose to put it in A1 and work down.

You can change this line to where you want the list to be placed:

Set DestCell = .Range("a1")

In fact, you could even put it on a different sheet:

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

becomes

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
End With

Set DestCell = worksheets("someothersheetnamehere").Range("x99")





Kim K wrote:

That is interesting, however, where the listbox is and the macro button, no
cells are merged, but at the top of the page I have merged cells, is this the
cause?
--
Thanks,,
Kim

"Dave Peterson" wrote:

Merged cells are going to screw up the routine anyway. That macro is going to
try to put a value in each of those cells (depending on how many were selected).

I'd unmerge those cells.

DestCell.Resize(.ListCount, 1).value = ""
might work, but I think you'll still have trouble with the rest of the code.


Kim K wrote:

will do thanks!

I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

--
Thanks,,
Kim

"Dave Peterson" wrote:

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

THANK SO MUCH! I got it all done with three multichoice listboxes with your
wonderful directions, thanks again!
--
Thanks,,
Kim


"Dave Peterson" wrote:

The button puts the selected items into a cell, er, a bunch of cells.

I chose to put it in A1 and work down.

You can change this line to where you want the list to be placed:

Set DestCell = .Range("a1")

In fact, you could even put it on a different sheet:

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

becomes

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
End With

Set DestCell = worksheets("someothersheetnamehere").Range("x99")





Kim K wrote:

That is interesting, however, where the listbox is and the macro button, no
cells are merged, but at the top of the page I have merged cells, is this the
cause?
--
Thanks,,
Kim

"Dave Peterson" wrote:

Merged cells are going to screw up the routine anyway. That macro is going to
try to put a value in each of those cells (depending on how many were selected).

I'd unmerge those cells.

DestCell.Resize(.ListCount, 1).value = ""
might work, but I think you'll still have trouble with the rest of the code.


Kim K wrote:

will do thanks!

I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

--
Thanks,,
Kim

"Dave Peterson" wrote:

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Glad you got it working.

Kim K wrote:

THANK SO MUCH! I got it all done with three multichoice listboxes with your
wonderful directions, thanks again!
--
Thanks,,
Kim

"Dave Peterson" wrote:

The button puts the selected items into a cell, er, a bunch of cells.

I chose to put it in A1 and work down.

You can change this line to where you want the list to be placed:

Set DestCell = .Range("a1")

In fact, you could even put it on a different sheet:

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

becomes

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
End With

Set DestCell = worksheets("someothersheetnamehere").Range("x99")





Kim K wrote:

That is interesting, however, where the listbox is and the macro button, no
cells are merged, but at the top of the page I have merged cells, is this the
cause?
--
Thanks,,
Kim

"Dave Peterson" wrote:

Merged cells are going to screw up the routine anyway. That macro is going to
try to put a value in each of those cells (depending on how many were selected).

I'd unmerge those cells.

DestCell.Resize(.ListCount, 1).value = ""
might work, but I think you'll still have trouble with the rest of the code.


Kim K wrote:

will do thanks!

I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

--
Thanks,,
Kim

"Dave Peterson" wrote:

Please keep the discussion in the newsgroup.

You'll be able to get quicker help that way.

Kim K wrote:

ok I see what I did wrong there and fixed that now I get a merged cell error
DestCell.Resize(.ListCount, 1).ClearContents

Can you email me at my email addy and let me send to you via email what I am
trying to do and what I have done so I can get help. I do not want to give
up I need to know this! Please let me know!

--
Thanks,,
Kim

"Dave Peterson" wrote:

Select the Listbox (just click on it or right click on it).
Then look at the namebox--to the left of the formula bar.

That's the name of the listbox.

You can either change the code to match the name or change the name to match the
code.

If you want to change the name, just select the listbox. Type the new name in
that namebox and hit enter.

One word of warning...

Make sure you used the listbox from the Forms toolbar--not from the control
toolbox toolbar.

If you change the listbox property to Multi, then the users can select multiple
items.

If they click on that adjacent button (also from the Forms toolbar), you can
populate some cells with their choices.

Kim K wrote:

Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim

"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson