VBA changes Max Value of Slider depending on other cell.

[Ochimus]

Using Excel 2010 I have a dropdown in cell B2 (EVENT) linked by Data Validation to a range in Column E (SLIST) of a database (SCHEDULE).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Each “event” has a different number of “active Days” listed in Column I.<o></o>
<o> </o>
I want the procedure to look at whatever is in B2, find the match in Column E, then make the number in Column I of that row the Max Value for the slider.<o></o>
<o> </o>
The Max Value will therefore change automatically when different “events” are selected in B2.<o></o>
<o> </o>
Tried using “INDEX/MATCH” as shown below but with no success<o></o>
<o> </o>

[COLOR=black][FONT=Verdana]Sub[/FONT][/COLOR][COLOR=black][FONT=Verdana] SETSLIDEVAL() 

ActiveSheet.Shapes.Range(Array("Scroll Bar 4")).Select 

With Selection 
.Value = 1 
.Min = 1 
'THIS NEXT LINE IS WHERE IT ALL GOES WRONG - DOESN'T LIKE "EVENT"
.Max = INDEX(SCHEDULE,MATCH(EVENT,SLIST,False),5) 
.SmallChange = 1 
.LargeChange = 10 
.LinkedCell = "C8" 
.Display3DShading = True 
End With 

End Sub [/FONT][/COLOR]

 

[JLGWhiz]

Using Excel 2010 I have a dropdown in cell B2 (EVENT) linked by Data Validation to a range in Column E (SLIST) of a database (SCHEDULE).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Each “event” has a different number of “active Days” listed in Column I.<o></o>
<o></o>
I want the procedure to look at whatever is in B2, find the match in Column E, then make the number in Column I of that row the Max Value for the slider.<o></o>
<o></o>
The Max Value will therefore change automatically when different “events” are selected in B2.<o></o>
<o></o>
Tried using “INDEX/MATCH” as shown below but with no success<o></o>
<o></o>

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]Sub[/FONT][/COLOR][COLOR=black][FONT=Verdana] SETSLIDEVAL() [/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]ActiveSheet.Shapes.Range(Array("Scroll Bar 4")).Select [/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]With Selection [/COLOR][/FONT]
[FONT=Verdana][COLOR=black].Value = 1 [/COLOR][/FONT]
[FONT=Verdana][COLOR=black].Min = 1 [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'THIS NEXT LINE IS WHERE IT ALL GOES WRONG - DOESN'T LIKE "EVENT"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black].Max = INDEX(SCHEDULE,MATCH(EVENT,SLIST,False),5) [/COLOR][/FONT]
[FONT=Verdana][COLOR=black].SmallChange = 1 [/COLOR][/FONT]
[FONT=Verdana][COLOR=black].LargeChange = 10 [/COLOR][/FONT]
[FONT=Verdana][COLOR=black].LinkedCell = "C8" [/COLOR][/FONT]
[FONT=Verdana][COLOR=black].Display3DShading = True [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End With [/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]End Sub [/COLOR][/FONT]
[/COLOR][/FONT]

I am surprised that you didn't get a different error message. To use a Worksheet Function in VBA code, it needs to be preceded by either Application, which only works for some of them, or WorksheetFunction, which works for all of them, like:

.Max = WorksheetFunction.INDEX(SCHEDULE,MATCH(EVENT,SLIST,False),5)

But I am not sure that even that would work with this particular formula.