Cell change event 1

[Matt27]

I am trying to write a bit of code in Visual Basic. I have a spreadsheet where cell F13 will be the User Name and cell N13 will be a date field to show the date they need their request completing. What I need to do in my code, is say when cell F13 is blank give a default date of 01/01/2010. When cell F13 has a name in it then leave cell N13 empty so the user can to enter a date. Is there an on change event I can use, or some other method to be able to do this?

 

[xlbo]

use the WORKSHEET change event and check the TARGET address to see which cell has been changed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Here is a good working basic structure for you ...

Option Explicit

Const MYRANGE As String = "N13"

Private Sub Worksheet_Change(ByVal Target As Range)
    
    '// Check if more than one cell is selected
    If Target.Cells.count > 1 Then Exit Sub
    
    '// Check the cell that changed
    If Intersect(Target, Me.Range(MYRANGE)) Is Nothing Then Exit Sub
    
    '// Turn events off to perform work and not loop
    Application.EnableEvents = False
    
    '// Perform action here
    '...
    
    '// Reset events
    Application.EnableEvents = True
    
End Sub

HTH

-----------
Regards,
Zack Barresse

 

[Matt27]

This is the code I have put in:-

Option Explicit
Const MYRANGE As String = "F13"
Private Sub Worksheet_Change(ByVal Target As Range)

'// Check if more than one cell is selected
If Target.Cells.Count > 1 Then Exit Sub

'// Check the cell that changed
If Intersect(Target, Me.Range(MYRANGE)) Is Nothing Then Exit Sub

'// Turn events off to perform work and not loop
Application.EnableEvents = False

'// Perform action here
If MYRANGE.Value = "" Then
Range("N15").Value = "01/01/2010"
Else
Range("N15").Value = ""
End If


'// Reset events
Application.EnableEvents = True

End Sub

So basically I want to say if cell F13 is blank then put a default value of 01/01/2010 in N15, otherwise if F13 has a value in it then leave N15 blank so the User can put a date in. However, when I put a value in F13 the above bit of code doesn't even run!! Any ideas??

 

[PHV]

Option Explicit
Const MYRANGE As String = "F13"
Private Sub Worksheet_Change(ByVal Target As Range)
'// Check if more than one cell is selected
If Target.Cells.Count > 1 Then Exit Sub
'// Check the cell that changed
If Intersect(Target, Range(MYRANGE)) Is Nothing Then Exit Sub
'// Turn events off to perform work and not loop
Application.EnableEvents = False
'// Perform action here
If [!]Range(MYRANGE)[/!].Value = "" Then
Range("N15").Value = "01/01/2010"
Else
Range("N15").Value = ""
End If
'// Reset events
Application.EnableEvents = True
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Matt27]

I've changed the code as you mentioned (highlighted in red) but it still isn't working!!

 

[SkipVought]

Why are you writing a STRING that looks like a date? Did you intend the value in N15 to be a REAL DATE?

Why do Dates and Times seem to be so much trouble? faq68-5827



Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[Matt27]

To be honest I don't really mind if the date is a string format or a date format so long as I can get this piece of code to work!!

 

[SkipVought]

Your statement, "To be honest I don't really mind if the date is a string format or a date format..." informs me that you do not understand Date/Time issues in Excel.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[Matt27]

I would need the cell in date format dd/mm/yyyy

 

[SkipVought]

You can format a REAL date 100 ways from Sunday.

A STRING is vitrually useless without performing contortions.

Did you read and understand the FAQ?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[Matt27]

Yes. Would putting the contents of this cell into a real date rather than a string make any difference to the code, i.e. would it actually work?

 

[SkipVought]

The code might work but the aftermath would not, I'd venture to day.

But that's like saying, "I just want the correct syntax, but I don't care at all what the code is doing!"

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

Check out the Format and DateSerial methods.

Example ..

    Range("N15").Value = DateSerial(2010, 1, 1)
    Range("N15").NumberFormat = "dd/mm/yyyy"

-----------
Regards,
Zack Barresse