Worksheet change event with cell linked to combo box result

[Fid]

I have cell K3 that is linked to a combo box. When the user picks a
value out of the combo box K3 changes to that value.

I need to run code when the value of K3 changes. I have the following
code:

Private Sub Worksheet_Change(ByVal Target As Range) '

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("K3")) Is Nothing Then
Hide_RowsPoochPad
End If

ws_exit:
Application.EnableEvents = True
End Sub

The worksheet_change event does not fire when the user selects an item
from the combo box and the value of K3 changes.

What do I need to do to get the code to run when the value of K3
changes? I tried using the combo box change event but that will not
suit my needs.

Thanks,

Brent

 

[Fid]

I cannot use Data-List because that would require the list to be on the
same worksheet. The list I need to reference is on another worksheet.

Any other suggestions?

Thanks,

Brent

 

[Tom Ogilvy]

You can use a defined name (Insert=>Name=>Define) to reference the list on
another sheet. Then use that name in your data validation

=List1

if list1 were the defined name.

 

[Fid]

Okay, so I have a named range on worksheet named "Data" called
"Style_List" that references the dynamic range
=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2).

When I have sheet "Specs" active and I do Data->List->Create List and
put "=Style_List" in the box asking me the reference to the list I get
the error:

The worksheet range for the list data must be on the active worksheet.

I am confused as to what I am doing wrong.

From what you are telling me I sould be able to refer to a named range

not on the active worksheet.

Thanks,

Brent

 

[Tom Ogilvy]

I did
Insert=>Name
Name: Style_List
RefersTo: =OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2)

Didn't make any difference what sheet was active.

To check the formula I went to the name box to the left of the formula bar
and entered

Style_List

it selected the list. (even changing the activesheet).

Then I went to a sheet other than data and did

Data=>Validation

Selected the List option

in the Source Box I put in

=Style_List

and OK'd out

Worked fine for me.

There should be data in At least 3 cells in column A of Data before
starting.

 

[Fid]

Tom,

Thank you very much for the solution. I the Data Validation working
with the named range reference.

Unfortunately this still does not solve my problem. I have two issues:

1. Using the Data Validation drop down list does not enable me to
control how many rows are shown when the drop down arrow is clicked nor
does it allow me to change the size and font of the drop down list.
For this reason I was trying to use a combo box.

2. The worksheet_change event still does not fire when a user selects
a different style from the data validation drop down list. I need to
run code based on this value changing.

The cell with the Data Validation list is B2. The code for the change
event is:

Private Sub Worksheet_Change(ByVal Target As Range) '

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("B2")) Is Nothing Then
Hide_RowsPoochPad
End If

ws_exit:
Application.EnableEvents = True
End Sub

Thanks,

Brent