Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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 "Craig" schreef in bericht ps.com... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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" schreef in bericht oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
Not Sure I understand what you're trying to say Gert. Please clarify if
you can Die_Another_Day |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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" schreef in bericht ups.com... Not Sure I understand what you're trying to say Gert. Please clarify if you can Die_Another_Day |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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" schreef in bericht ups.com... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
Oops Cells(ComboBox1.ListIndex,234etc) should read:
Cells(CombBox1.ListIndex,2).VALUE Try That Die_Another_Day |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
Thanks a lot; this part works! But there are some errors; in a couple of
minutes I will post a message. "Die_Another_Day" schreef in bericht ups.com... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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" schreef in bericht ups.com... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform for displaying values from a row
If you are also jgmiddel, then you have another response at your other thread.
Gert-Jan wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying cell values in a Userform | Excel Discussion (Misc queries) | |||
Userform for displaying values from a row | Excel Discussion (Misc queries) | |||
Displaying one userform from another | Excel Programming | |||
displaying picture in a userform | Excel Programming | |||
Displaying progress on UserForm | Excel Programming |