Hello All,

I am currently developing a template in excel. I am new to VBA and
have been slowly learning it since last week.

Here is a brief description of my template. Many thanks to everyone
who can provide suggestions and help me figure this out.

Background

I have a table RAWDATA which has the following columns
country group model segment 2005 2006 2007
model is key column in this table, there can be several instances of
the same model but each instance would have unique values in country
2005, 2006 and 2007 columns

What I have done so far

I have created a userform

Userform1 which has a listbox containig unique model values from the
model column. I have also created some labels and a few textboxes for
user inputs

When the user doubleclicks on one of the models (say model 1)
I have connected the first labelcaption to the textbox choice

''label1.caption = listbox1.text''

at the same time the table RAWDATA is filtered according to
listbox1.text

From this I want

first to count how many instances of the model are present in RAWDATA
enable labels
label2 to display country column value
label3 to display 2007 column value

example(if i choose model-1 from the list) then label1.caption =
model1 and RAWDATA list is filtered according to model 1

from this i would like to count how many rows of model 1 are present
in the table
if 3 then 3 labels should be enabled
and each labelcaption should display one country value from each row

similarly i would like to display 2007 column values on another set of
3 labels

by default all labels should be disabled

I used index and match functions (because vlookup would not work on
any column)
by recording a macro

on the excel the index and match function work perfectly fine. when i
look into the code however it is formulated in relative terms and does
not work in the vba because when the sheet is filtered models are not
necessarily in consecutive rows

ActiveCell.FormulaR1C1 = _
"=INDEX('Raw Data'!R[1]C[-4]:R[673]C[14],MATCH('Raw Data'!
R[1]C,'Raw Data'!R[1]C:R[673]C,0),2)"

The above is the fromula that is displayed after i record a macro.


Hope my explanation is clear and any help is appreciated

Thanks
V-

Label1's caption is depended Combobox1 value. if that is what you trying to
accomlish...
Try using Somthing like this

Option Explicit

Private Sub ComboBox1_Click()
If ComboBox1.Value = "Item 1" Then
Label1.Caption = "Your Caption"
ElseIf ComboBox1.Value = "Item 2" Then
Label1.Caption = "My Caption"
End If
End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "Item 1"
ComboBox1.AddItem "Item 2"
End Sub


" wrote:

Hello All,

I am currently developing a template in excel. I am new to VBA and
have been slowly learning it since last week.

Here is a brief description of my template. Many thanks to everyone
who can provide suggestions and help me figure this out.

Background

I have a table RAWDATA which has the following columns
country group model segment 2005 2006 2007
model is key column in this table, there can be several instances of
the same model but each instance would have unique values in country
2005, 2006 and 2007 columns

What I have done so far

I have created a userform

Userform1 which has a listbox containig unique model values from the
model column. I have also created some labels and a few textboxes for
user inputs

When the user doubleclicks on one of the models (say model 1)
I have connected the first labelcaption to the textbox choice

''label1.caption = listbox1.text''

at the same time the table RAWDATA is filtered according to
listbox1.text

From this I want

first to count how many instances of the model are present in RAWDATA
enable labels
label2 to display country column value
label3 to display 2007 column value

example(if i choose model-1 from the list) then label1.caption =
model1 and RAWDATA list is filtered according to model 1

from this i would like to count how many rows of model 1 are present
in the table
if 3 then 3 labels should be enabled
and each labelcaption should display one country value from each row

similarly i would like to display 2007 column values on another set of
3 labels

by default all labels should be disabled

I used index and match functions (because vlookup would not work on
any column)
by recording a macro

on the excel the index and match function work perfectly fine. when i
look into the code however it is formulated in relative terms and does
not work in the vba because when the sheet is filtered models are not
necessarily in consecutive rows

ActiveCell.FormulaR1C1 = _
"=INDEX('Raw Data'!R[1]C[-4]:R[673]C[14],MATCH('Raw Data'!
R[1]C,'Raw Data'!R[1]C:R[673]C,0),2)"

The above is the fromula that is displayed after i record a macro.


Hope my explanation is clear and any help is appreciated

Thanks
V-