Solved: Code example required please?

[Aussiebear]

Can someone show me an example of how to, on a form which displays the individual days in any one month, so that if I were to select any month, my form will show similar to a bank statement? Some software allows you to pick the start and end dates from a pop up calendar, whilst others require you to enter the dates.

In the attached example, I have simply allowed for an input from the operator to determine the start and end dates, and would love to have the cells A13 to A42 reflect the days within the period.

If there's an other section which someone has an example of automating by code, please feel free to show me how to do so. Opps... that more than one question in a post. My apologies to the Admin staff.

[Bob Phillips]

You can't really expect help when you password protect the worksheet.

[Aussiebear]

LOL... Sorry but its lat at night and I'm going cross eyed

[gareth]

Hi Aussiebear,

Would you be so kind as to explain in more detail exactly what you need. Your response is a bit limited. . . . Also I think there must be another date column in order to calcualte correctly if you want to see the amount of days it fluxuates from

Regards
gareth

[Aussiebear]

Certainly can. When I select a month, any month of the year, I'd like the range currently occupied by A13 to A42 to reflect the number of days in the month selected. When I understand how it operates I wish to use it as the example I posted displays. Some software allows the dates to be chosen from a pop up calendar, whilst others require you to enter in the actual start and end dates for the period you want.

At the moment the example shows two data entry cells to determine the period under selection, but its not yet linked to the month range A13 to A42, but will be later on if someone can show me how to go about it. It may well be that someone has a method of creating a popup calendar to enable the data operator to choose from, but for the moment I'm going with a manual data entry method.

I intend to use this example as a method to check the interest component being calculated on an account. And yes there are plenty of account checking software packages out there but... I wanted to have a go at something like this myself.

There are some very clever people here who use this forum, who may like to help this mentally challanged individual if they are so inclined.

[Aussiebear]

Hmmm.... seems I'm on my own here... Oh well so be it then.

[Aussiebear]

Okay have had a sleep on this issue, and a rethink about how to describe just waht it is that I'm trying to do.

Please refer to the attached sample for reference.

My problem is that I need a dynamic range that will fill to a maximum size of A14:F44 depending on the date range the user inputs into cells B7 & C7. Should the date range be only 28 days then the dynamic range needs only to be from A14:F41.

Can this be triggered once the date range is known (the second date is entered into C7)?

I do apologise if there was some confusion before, but when you are tired things are as clear as mud.

If you click on the Module 1 code, you can see that I'm stuck with entering the possible date range by long code methodology, and I have no current way of stopping the new date being entered when it goes beyond the date value in C7.

[geekgirlau]

Hi Aussie,

Try this for starters:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer


' change to period dates?
If Not Intersect(Target, Range("Period")) Is Nothing Then
' clear existing dates
Range("DateList").ClearContents

' create list of dates
For i = 0 To DateDiff("d", Range("StartDate"), Range("EndDate"))
Range("A14").Offset(i, 0) = Range("StartDate") + i
Next i
End If
End Sub


When you have a look at the workbook, you'll notice that the range "DateList" is dynamic. This means it will automatically adjust as dates are added or removed.

In order to stop dates being entered outside of these values, you could manage this with sheet protection (you will have to modify the code to turn the protection off then back on again).

[Aussiebear]

G'day Geek, Thank you for the quick reply. I notice it runs the formatting down to row 75, is there any way of clearly defining the dynamic range to only run until it reaches the date value in C7?


Hi Aussie,

Try this for starters:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer


' change to period dates?
If Not Intersect(Target, Range("Period")) Is Nothing Then
' clear existing dates
Range("DateList").ClearContents

' create list of dates
For i = 0 To DateDiff("d", Range("StartDate"), Range("EndDate"))
Range("A14").Offset(i, 0) = Range("StartDate") + i
Next i

'(Can we add a limit in here at this point that says when ActiveCell.Value = the value in C7,
'stop the process?. At any one time I just want to be able to view those days in the month
'selected.)

End If
End Sub

When you have a look at the workbook, you'll notice that the range "DateList" is dynamic. This means it will automatically adjust as dates are added or removed.

There's that "Intersect" thing.. one more thing to look up and remember.



In order to stop dates being entered outside of these values, you could manage this with sheet protection (you will have to modify the code to turn the protection off then back on again).

I had intended to have the sheet protected but only so the user will have no access to any cells other than those where an input is required.

Ted

[mdmackillop]

Here's a modification of GG's code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
On Error GoTo Exits
Application.EnableEvents = False
' change to period dates?
If Not Intersect(Target, Range("Period")) Is Nothing Then
' clear existing dates
On Error Resume Next
With Range("DateList").Resize(, 6)
.ClearContents
.Interior.ColorIndex = 35
End With
On Error GoTo Exits
' create list of dates
For i = 0 To DateDiff("d", Range("StartDate"), Range("EndDate"))
Range("A14").Offset(i, 0) = Range("StartDate") + i
Next i
End If
With Range("DateList")
.Offset(, 0).Interior.ColorIndex = 15
.Offset(, 1).Interior.ColorIndex = xlNone
.Offset(, 2).Interior.ColorIndex = xlNone
.Offset(, 3).Interior.ColorIndex = 15
.Offset(, 4).Interior.ColorIndex = xlNone
.Offset(, 5).Interior.ColorIndex = 15
End With



Exits:
Application.EnableEvents = True

End Sub


To get your calendar working, add the following
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B7,C7")) Is Nothing Then
frmCalendar.Show
End If
End Sub


GG, You need to add the EnableEvents lines within an Event driven macro. Try stepping through your code and you'll see it keeps restarting. You're getting the result here, but that won't always be the case
Regards
MD

[Aussiebear]

MD, thank you for your help here. I'm struggling to understand what the code is doing, so if you have a moment or two could you please break it down into sections and tell me more about it please.

Ted

[mdmackillop]

Hi Ted,
A bit more detail

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
On Error GoTo Exits
'Prevent macro from restarting when changes are made by the code
Application.EnableEvents = False
'Only run if cell in specified range is changed
If Not Intersect(Target, Range("Period")) Is Nothing Then
'If DateList has been cleared, procede with next step
On Error Resume Next
'Extend range of DateList to include 6 columns to the right
'clear contents and return colour to green
With Range("DateList").Resize(, 6)
.ClearContents
.Interior.ColorIndex = 35
End With
'Return to default error handling
On Error GoTo Exits
'Create list of dates
For i = 0 To DateDiff("d", Range("StartDate"), Range("EndDate"))
Range("A14").Offset(i, 0) = Range("StartDate") + i
Next i
End If
'Change colour of cells offset from DateList by specified number of columns
With Range("DateList")
.Offset(, 0).Interior.ColorIndex = 15
.Offset(, 1).Interior.ColorIndex = xlNone
.Offset(, 2).Interior.ColorIndex = xlNone
.Offset(, 3).Interior.ColorIndex = 15
.Offset(, 4).Interior.ColorIndex = xlNone
.Offset(, 5).Interior.ColorIndex = 15
End With
'Enable change events for sheet
Exits:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Only run if cell in specified range is selected
If Not Intersect(Target, Range("B7,C7")) Is Nothing Then
frmCalendar.Show
End If
End Sub

[Aussiebear]

We are cooking with gas now... Thankyou MD for you explanation.

[Aussiebear]

In a standard spreadsheet the value of cell D14 could be written as =Offset(D14,-5,1)-B14+C14 to give the initial starting monthly ( End of day) balance, but because we are creating the Datelist Range with an event procedure do i need to then create an Event macro to install the value required?

If the above is true, is this the sort of thing I need to write?

Sub IntEODBalance Macro()
'
' IntEODBalance Macro recorded 4/7/2006 by Ted

Range("D14")
ActiveCell.FormulaR1C1 = "=Offset(D14,-5,1)-B14+C14"

End Sub

[mdmackillop]

Hi Ted,
The simplest way to get the code is to use the macro recorder, making sure you use relative references. In this case, you should get something like

ActiveCell.FormulaR1C1 = "=R[-5]C[1]-RC[-2]+RC[-1]"


In this case, you want the value from E9, you need to "fix" this reference so change the first line to
ActiveCell.FormulaR1C1 = "=R9C5-RC[-2]+RC[-1]"

You then just need to change the cell and add this to your loop in your code which inserts the date
' create list of dates
For i = 0 To DateDiff("d", Range("StartDate"), Range("EndDate"))
Range("A14").Offset(i, 0) = Range("StartDate") + i
Range("A14").Offset(i, 3).FormulaR1C1 = "=R9C5-RC[-2]+RC[-1]"
Next i
With this latter line working, it should be apparent how to adjust the code to add a suitable formula to the Daily Interest column.
Regards
MD

[geekgirlau]

GG, You need to add the EnableEvents lines within an Event driven macro. Try stepping through your code and you'll see it keeps restarting. You're getting the result here, but that won't always be the case
Regards
MD

MD, absolutely spot on. I must admit that I always forget to do this first time around, and only remember during testing when the @#$%! event keeps restarting! :omg2:

[Aussiebear]

MD, I had been trying to use the recorder but had referenced the cells incorrectly. It kept coming up with #Name errors for the referenced cells.

:friends: Thanks for your help.

A question that comes to mind here is how does a range reference know how big a range is? One could say for example "Range ("A14")" to select a single cell, or "Range("A14:A30") for a known range, but how does a range reference know what a dynamic range is? I have seen code refer to " Range ("B6" & cRow)". Does the reference "cRow" imply the rest of the column?

[mdmackillop]

Hi Ted,
Your example would be Range("B6:B" & Crow), where cRow is a variable given a value or deriving a value from elsewhere in the code. You may want, for example to select all the cells in B, to the last entry in column A. In this case you can use cRow = [A65536].End(xlup).row. If you want the rest of the column, you would use Range("B6:B65536")