Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event with cell linked to combo box result
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event with cell linked to combo box result
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 Jim Jackson wrote: What if, instead of the Combo-Box, you use a list. "Data/List/..." for the source range of the choices. Then Data/Validation with K3 selected. -- Best wishes, Jim "Fid" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event with cell linked to combo box result
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. -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... 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 Jim Jackson wrote: What if, instead of the Combo-Box, you use a list. "Data/List/..." for the source range of the choices. Then Data/Validation with K3 selected. -- Best wishes, Jim "Fid" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event with cell linked to combo box result
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 Jim Jackson wrote: As always, I only say half what I am thinking. Thanks, Tom for telling "the rest of the story." -- Best wishes, Jim "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... 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 Jim Jackson wrote: What if, instead of the Combo-Box, you use a list. "Data/List/..." for the source range of the choices. Then Data/Validation with K3 selected. -- Best wishes, Jim "Fid" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event with cell linked to combo box result
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. -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... 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 Jim Jackson wrote: As always, I only say half what I am thinking. Thanks, Tom for telling "the rest of the story." -- Best wishes, Jim "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... 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 Jim Jackson wrote: What if, instead of the Combo-Box, you use a list. "Data/List/..." for the source range of the choices. Then Data/Validation with K3 selected. -- Best wishes, Jim "Fid" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event with cell linked to combo box result
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 Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... 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 Jim Jackson wrote: As always, I only say half what I am thinking. Thanks, Tom for telling "the rest of the story." -- Best wishes, Jim "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... 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 Jim Jackson wrote: What if, instead of the Combo-Box, you use a list. "Data/List/..." for the source range of the choices. Then Data/Validation with K3 selected. -- Best wishes, Jim "Fid" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What cell caused the worksheet change event | Excel Programming | |||
Using linked cell to change value in combo box | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |