Excel VBA Procedure - Cannot clear a cell after populated via procedure

excel , vba procedures , worksheet_change Minneapolis, United States
  • 12 years ago
    Trying to populate a cell (C) based on value in another cell (B). When the value in cell B is cleared, I need cell C to be cleared automatically. Here is my current code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("B28").Address Then If Target.Address <> "" Then Range("C28").Value = Date Range("C28").Select Selection.NumberFormat = "YYYY-MM-DD" End If Else If Target.Address = "" Then Range("C28").Select Range("c28").Value = "" End If End If Users will populate values in select cells in column B from a drop down. While the code works as it is now, the user might need to change the value in cell B28. As it sits now, the value in C28 will still read the current date. I need that value to disappear. Please help. I have spent at least 10 hours trying to resolve this myself. Incendentally, this is my first pass at vba procedures. I have created many vba excel macros' but procedures are all new to me. Thank you in advance, Barbara
  • 12 years ago
    Hi, I think the logic is wrong in this and the second inner if is never being executed, try the following: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("B28").Address Then If Target.Address <> "" Then Range("C28").Value = Date Range("C28").Select Selection.NumberFormat = "YYYY-MM-DD" Else If Target.Address = "" Then Range("C28").Select Range("c28").Value = "" End If End If Aquila

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.” - Rick Osborne