Excel 2000, VBA for combo box on form

  • Thread starter Thread starter Greg Simms
  • Start date Start date
G

Greg Simms

Hello, this is a tad complicated I grant you, however here goes.

How do I get a combo box on a form to look at a range in Two columns on a
worksheet.
Then when a selection is made from the combo box, this selection is then
placed elsewhere on the same worksheet

EG: Range for combo box to look at = N90:O140. Range for placement of
selection is G31:H43
In cell N92 is "A82", In Cell O92 is "Tricolour stainless steel fan".
(Ignore quotaion marks)

When this particular selection is chosen from the combobox, in cell G31
"A82" is placed, and in Cell H31 "Tricolour stainless steel fan" is placed.

The placement of the data wants to fall in to the first free cells in the
range G31:H43.
So if cells G31 & H31 are already occupied, then the placement of the
selection goes in to G32 & H32, unless these are occupied, then G33 &H33.etc
etc.

..

Thanking you all in Advance

cordially yours
Greg Simms
 
How do I get a combo box on a form to look at a range in Two columns on a
worksheet.
Then when a selection is made from the combo box, this selection is then
placed elsewhere on the same worksheet

EG: Range for combo box to look at = N90:O140. Range for placement of
selection is G31:H43
In cell N92 is "A82", In Cell O92 is "Tricolour stainless steel fan".
(Ignore quotaion marks)

When this particular selection is chosen from the combobox, in cell G31
"A82" is placed, and in Cell H31 "Tricolour stainless steel fan" is placed.

The placement of the data wants to fall in to the first free cells in the
range G31:H43.
So if cells G31 & H31 are already occupied, then the placement of the
selection goes in to G32 & H32, unless these are occupied, then G33 &H33.etc
etc
Click to expand...

Go to menu insert/name/define and type name "go" refers to:
=OFFSET(Sheet1!$G$31;COUNTA(Sheet1!$G:$G);0;)
(I assume yor worksheet is named Sheet1, no other cells filled
in column G, except G30 which is a column header, if not - you need to
adjust the formula).

In combo box properties:
ListFillRange > N90:O140
ColumnCount > 2
LinkedCell > go

In the ComboBox module use the following code:

Private Sub ComboBox1_Change()

Range("go").Offset(-1, 1) = ComboBox1.List(ComboBox1.ListIndex, 1)

End Sub

If the combobox is placed in your worksheet the code to be placed in Sheet1
module,
if it's placed on aUserForm it must be placed in the forms module.
 
Hello, many thanks for your reply, however when your formula in define name
is showing an error.
the following is highlited as the error $G$31.

Having not used the OFFSET or COUNTA functions before, I am not sure as to
why.

Greg Simms
 
mk uses semicolons for his separator:
=OFFSET(Sheet1!$G$31;COUNTA(Sheet1!$G:$G);0;)

I use the comma for mine.
=OFFSET(Sheet1!$G$31,COUNTA(Sheet1!$G:$G),0,)

When you type a formula like: =if(a1=7,3,5)
What do you use as the separator--whatever it is, match it in your name formula.

Debra Dalgleish has some instructions for this kind of thing at:
http://www.contextures.com/xlNames01.html#Dynamic
 
mk uses semicolons for his separator:
=OFFSET(Sheet1!$G$31;COUNTA(Sheet1!$G:$G);0;)

I use the comma for mine.
=OFFSET(Sheet1!$G$31,COUNTA(Sheet1!$G:$G),0,)

When you type a formula like: =if(a1=7,3,5)
What do you use as the separator--whatever it is, match it in your name formula.
Click to expand...
Right, that's my regional settings.
If you use comma as list separator ypu must use comma, not semicolon.

If you do not like the idea with named range: the alternative is to use the
following code:
It assumes that you already have some data in the region starting in G31, or
at least
column headers in G31 and H31:

Private Sub ComboBox1_Change()

Dim myrange As Range
Set myrange = Cells(Range("G31").CurrentRegion.Rows.Count + 1, 7)

myrange = ComboBox1.List(ComboBox1.ListIndex, 0)
myrange.Offset(0, 1) = ComboBox1.List(ComboBox1.ListIndex, 1)

End Sub
 
Thank you, I have replaced the ; with a , however in the properties window
for the combo box,
their is no ListFillRange, and LinkedCell, also if I get an error mesage
(Invalid Propert Value) when I type >2 in the ColumnCount.

Because of the above, I have not tried out any of the code.

As for your new set of code, Dim my range etc etc,
I havent tried this code, because in cells G31 through to H43 the cells are
all blank. In cell G30:H30 there are complicated Index and match formulas
which cannot be overwritten.

Please suggest further Ideas, or fixes.

Many Thanks

Greg Simms
 
When those kinds of comboboxes are placed on the worksheet, they have those
properties.

When they're on a userform, they're called something different:

listfillrange becomes RowSource
linkedcell becomes controlsource
columncount becomes columncount <bg>

I created a form with a combobox and a commandbutton on it and and used this
code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < -1 Then
Me.CommandButton1.Enabled = False
Else
Me.CommandButton1.Enabled = True
End If
End Sub

Private Sub CommandButton1_Click()
Dim destCell As Range

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

With Worksheets("sheet1")
Set destCell = .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0)
If destCell.Row < 31 Then
Set destCell = .Range("G31")
End If
End With

With Me.ComboBox1
destCell.Value = .List(.ListIndex, 0)
destCell.Offset(0, 1).Value = .List(.ListIndex, 1)
End With

Unload Me

End Sub

Private Sub UserForm_Initialize()
With Me.ComboBox1
.ColumnCount = 2
.RowSource _
= Worksheets("sheet1").Range("N90:O140").Address(external:=True)
.Style = fmStyleDropDownList
End With

Me.CommandButton1.Enabled = False
End Sub

I did assume that all this stuff was happening to Sheet1.
 
Thank you, I will now try out all the various pieces of help that I have
recieved.

Thanks again

Greg Simms
 
Hello, Okay, the following did not quite work.

Go to menu insert/name/define and type name "Fans" refers to:
=OFFSET(Quote!$G$31,COUNTA(Quote!$G:$G),0,)
(I assume yor worksheet is named Sheet1, no other cells filled
in column G, except G30 which is a column header, if not - you need to
adjust the formula).

In combo box properties:
Row Source N90:O140
ColumnCount 2
Control Source Fans.

In the Control Source it comes back with an error message, Invalid Property
Value.
So I set the control source as G31

' This code crashes possibly due to the above
Private Sub ComboFans_Change()

Range("go").Offset(-1, 1) = ComboFans.List(ComboFans.ListIndex, 1)

End Sub

In Cells G30 and H30, there are complicated Index and match formulas which
are working for another ComboBox on the
Quote worksheet, so these cannot be overwritten.

On the Quote worksheet their is a button, which when clicked pops up a
userform, which has Checkbox items on it.
When any of these items is chosen, it places text in the first available
cell from G31 to G43.
In cells H31 to H43, index and match formulas match a product with the text,
that has been placed by the checkbox items.

The combofan box, lists all of a range of fans from N90:O140, (See row
source above)
when a fan is chosen from this Combo, the code needs to check the first
availble cell from G31to G43, then place
the first part of the chosen fan, (which will be somewhere From N90:N140) in
this cell.

As an example. If In cell N100, I have the text"A50" and in cell O100 I have
"Gold Ceilng fan". (Ignore Quotation marks)
When the combobox chooses this one, it places A50, in the first available
Cell from G31 to G43.

Then if I choose any more items from the various checkboxes, these will then
be placed in the first availble cell within the same range of G31:G43.

The above code as mentioned by MK is very close to working, and is the only
answer that I have tried, as I do not know enough about VB, to understand
the other answers, and has to how I can relate them to my worksheet.

Although I must thank all who answered, as it is not allways easy to
undestand exactly what a person is trying to achieve
when all you have to go by is a text post.

anymore help would be very much appreciated.

Thanking you all in advance

Greg Simms





Greg Simms said:
Thank you, I will now try out all the various pieces of help that I have
recieved.

Thanks again

Greg Simms
Dave Peterson said:
When those kinds of comboboxes are placed on the worksheet, they have those
properties.

When they're on a userform, they're called something different:

listfillrange becomes RowSource
linkedcell becomes controlsource
columncount becomes columncount <bg>

I created a form with a combobox and a commandbutton on it and and used this
code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < -1 Then
Me.CommandButton1.Enabled = False
Else
Me.CommandButton1.Enabled = True
End If
End Sub

Private Sub CommandButton1_Click()
Dim destCell As Range

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

With Worksheets("sheet1")
Set destCell = .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0)
If destCell.Row < 31 Then
Set destCell = .Range("G31")
End If
End With

With Me.ComboBox1
destCell.Value = .List(.ListIndex, 0)
destCell.Offset(0, 1).Value = .List(.ListIndex, 1)
End With

Unload Me

End Sub

Private Sub UserForm_Initialize()
With Me.ComboBox1
.ColumnCount = 2
.RowSource _
= Worksheets("sheet1").Range("N90:O140").Address(external:=True)
.Style = fmStyleDropDownList
End With

Me.CommandButton1.Enabled = False
End Sub

I did assume that all this stuff was happening to Sheet1.
Click to expand...
cells
Click to expand...
 
Back
Top