I am trying to make dyanmic scrollable charts from a dyanmic range being filled up using vlookup from
a bigger range. Now for vlooking I need somthing for reference to be dyanmically filled there so that the rest of the values
which will make up the chart could be calculated.
So for filling up those I wished to use worksheet_change to track the change in linked cell of that scroll bar. (Actually the
scroll bar can go from 1 to 12 representing months, and each month then has different values which are then displayed in the
So the code I was using is:
Private Sub worksheet_change(ByVal Target As Range)
Dim i As Integer
Dim m As String
Dim j As Integer
m = selectedmonth
If Target.Address = "$H$28" Then
For i = 0 To Noofdays
Range("34+i:8").Value = m & (i + 1)
For j = Noofdays + 1 To Noofdays + 10
Range("34+j:8").Value = ""
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
selectedmonth is jan,feb, mar etc depending upon the value in linked cell and Noofdays is no of working days in the
month. Both are named ranges in that sheet.
But then I learnt that A Worksheet_Change event: triggers when you
change a cell (or range of cells) value manually or in a macro -- it will not be triggered from a change showing up in a
formula or from a change of format. So a worksheet_change would not trigger on change made by scroll bar.
decided to go for worksheet_calculate but it then started giving all sorts of errors.
Hope I have made my problem
clear and hope someone can help!!