Userform for displaying values from a row

[Gert-Jan]

Hi,

In the range A1 - G20 I have values. Range A1 - A20 is called "products". In
the other coloms there are values of each specific product.
I would like to receive some help creating a userform with a ComboBox,
labels and buttons. The ComboBox should -on change- display the values of
the specific row in the labels. That is not the problem; I can do that.
But also: I would like to create a button "next" and "previous". And when
there is no possiblity to go back or further; the option must be disabled.

Any help would be appriciated.

 

[Craig]

Gert, I was just about to post a question asking how to change the
labels for specifics within a worksheet. Would you mind posting how
you're doing that?

 

[Gert-Jan]

ComboBox1 -> on change: value to range (Sheet2A1)
On sheet2 I get the values with a Excel-formula in a range
On userform: get the values from Sheet2

 

[Die_Another_Day]

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = ComboBox1.ListCount Then
CommandButton1.Enabled = False
If ComboBox1.ListIndex = 1 Then CommandButton2.Enabled = False
End Sub

Private Sub CommandButton1_Click() 'Next Button
Dim i As Long 'Index
i = ComboBox1.ListIndex
If i < ComboBox1.ListCount Then
ComboBox1.ListIndex = i + 1
If i + 1 = ComboBox1.ListCount Then CommandButton1.Enabled = False
Else
CommandButton1.Enabled = False
End If
End Sub

Private Sub CommandButton2_Click() 'Previous Button
Dim i As Long 'Index
i = ComboBox1.ListIndex
If i > 1 Then
ComboBox1.ListIndex = i - 1
If i - 1 = 1 Then CommandButton2.Enabled = False
Else
CommandButton2.Enabled = False
End If
End Sub

I think that would complete the task

Die_Another_Day

 

[Gert-Jan]

Thanks a lot.
But there is one thing: I didn't mention that it will be better to do it all
in VBA. If there are non-unique items in my range, I will have a problem.

 

[Die_Another_Day]

Not Sure I understand what you're trying to say Gert. Please clarify if
you can

Die_Another_Day

 

[Gert-Jan]

My problem is: how to get the values that are in the coloms B, C, D etc in
my labels?
What I did was the following:
ComboBox1 -> on change: selected value to a cell
with formulas (index, compare) I got the values for the selected product in
colom A.
ComboBox1 -> on change: get values for labels from Excel (see above)
The problem is: if there are some records with the same value; the formula
onl will pick up the first.

I'm sorry I cannot explain better; I work with the Dutch version of Excel; I
don't know the exact translation for Excel formulas. Hopefully it is clear
to you now.

 

[Die_Another_Day]

If I understand what you're saying you need to use the index off of the
ComboBox not the Value. This allows you to get around duplicate entry's
in Column A. Therefore your ComboBox1_Change() Should look something
like this:

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = ComboBox1.ListCount Then
CommandButton1.Enabled = False

If ComboBox1.ListIndex = 1 Then CommandButton2.Enabled = False
Label1.Value = Cells(ComboBox1.ListIndex,2) 'When using the "Cells"
approach, the first argument is the row, the second argument is the
column. In this case I'm using ComboBox1.ListIndex to set the row which
is what I think you're trying to do, 2 references column B
Label2.Value = Cells(ComboBox1.ListIndex,3) 'Column C and so and so
forth for remaining Labels
End Sub

If this is not what you are after then please post as much of your code
as possible and I'll help if I can.

Die_Another_Day

 

[Gert-Jan]

You understand me well: when the selection in ComboBox1 is the value from
A10 in label1 should be the value of B10, in label2 the value of C10, etc.

I changed you code a little (label.value doesn't exist: label.caption)

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = ComboBox1.ListCount Then
CommandButton1.Enabled = False
If ComboBox1.ListIndex = 1 Then CommandButton2.Enabled = False
Label1.Caption = Cells(ComboBox1.ListIndex, 2)
Label2.Caption = Cells(ComboBox1.ListIndex, 3)
Label2.Caption = Cells(ComboBox1.ListIndex, 4)
End If
End Sub

Unfortunually, it doesn't work...

 

[Die_Another_Day]

Oops Cells(ComboBox1.ListIndex,234etc) should read:

Cells(CombBox1.ListIndex,2).VALUE

Try That

Die_Another_Day

 

[Die_Another_Day]

Here is an easier to read version:

Private Sub ComboBox1_Change()
Dim i as Long 'Index
i = ComboBox1.ListIndex
If i = ComboBox1.ListCount or i = 1 Then CommandButton1.Enabled =
False
Label1.Caption = Cells(i, 2).Value
Label2.Caption = Cells(i, 3).Value
Label3.Caption = Cells(i, 4).Value
Label4.Caption = Cells(i, 5).Value
Label5.Caption = Cells(i, 6).Value
Label6.Caption = Cells(i, 7).Value
End Sub

Let me know if that gets the job done

Die_Another_Day

 

[Gert-Jan]

Thanks a lot; this part works! But there are some errors; in a couple of
minutes I will post a message.

 

[Gert-Jan]

Hi,

Thanks a lot for helping! The labels are getting filled now, but there are
some errors:

- after opening the userform: when I select the first item: I get an error
from VBA.
- when the second item is selected; the button "next" is disabled.
- when the last item is selected, the "next" button is still enabled

This is what I got till now:

Private Sub ComboBox1_Change()
Dim i As Long 'Index
i = ComboBox1.ListIndex
If i = ComboBox1.ListCount Or i = 1 Then CommandButton1.Enabled = False
Label1.Caption = Cells(i, 2).Value
Label2.Caption = Cells(i, 3).Value
Label3.Caption = Cells(i, 4).Value
'etc
End Sub
Private Sub CommandButton1_Click() 'Next Button
Dim i As Long
i = ComboBox1.ListIndex
If i < ComboBox1.ListCount Then
ComboBox1.ListIndex = i + 1
If i + 1 = ComboBox1.ListCount Then CommandButton1.Enabled = False
Else
CommandButton1.Enabled = False
End If
End Sub

Private Sub CommandButton2_Click() 'Previous Button
Dim i As Long
i = ComboBox1.ListIndex
If i > 1 Then
ComboBox1.ListIndex = i - 1
If i - 1 = 1 Then CommandButton2.Enabled = False
Else
CommandButton2.Enabled = False
End If
End Sub

 

[Die_Another_Day]

This is one of those "Is Zero a number" things in this case yes Zero is
a number, this accounts for all problems

"- after opening the userform: when I select the first item: I get an
error from VBA."
When the first item is selected the index is Zero, however there is no
Zero row in Excel.

"- when the second item is selected; the button "next" is disabled."
The second item is now "1" therefore we told VBA to disable the button

"- when the last item is selected, the "next" button is still enabled"
The ".ListCount" is a true count, the max Index is Count - 1 so we
never satisfy "=.ListCount"


So to fix all these problems all we have to do is change this line:

i = ComboBox1.ListIndex

to this:

i = ComboBox1.ListIndex + 1 in all 3 subs

Die_Another_Day

 

[Dave Peterson]

If you are also jgmiddel, then you have another response at your other thread.