Excel VBA Cell value changes

Microsoft / Office 2002 sp3
May 9, 2010 at 06:38:14
Specs: Windows XP
Hi all, can you help please -
When a date (dd-mmm-yy) is entered into any row column 'W' Copy it to same row column 'AU' .
When a date is removed from any row column 'W' enter 'system date' in same row column 'AV' and trigger a message box with options 'Gratis' or 'Charged'. Enter response in same row column 'W'. If 'Gratis' format cell black with white font else format cell green with black font.
Thanking you in anticipation
John

See More: Excel VBA Cell value changes

Report •

#1
May 9, 2010 at 07:23:55
An explanation -
A levy (20) is applied to column 'U' when a date is entered in column 'W'. The user has discression to waiver or charge the levy. However, no record is kept of what happened.
My request above covers the event where the user simply deletes the date in column 'W' thereby removing all record of the levy ever having existed. But, the user could delete the levy in column 'U' and again no record would exist as to whether the levy had been waivered or charged.

Would it be possible to include code for this event -
When column 'U' is blanked or set to zero AND a date exists in column 'W' then the message box above would also be triggered


Report •

#2
May 9, 2010 at 08:57:53
When you say:

A levy (20) is applied to column 'U' when a date is entered in column 'W'.

Does this mean that the user also enters the value in Column U or is it done automatically?

The reason I ask is that it might be easier to restrict user changes to Column U instead of trying to capture the event via code.

In the meantime, try this code for your original request.

Instead of gong with a fancy UserForm with buttons for Charged and Gratis, I opted for the simpler MsgBox with Yes No & Cancel buttons.

As per the Prompt, Yes means Charged, No means Gratis and Cancel means "Whoops, I didn't mean to delete that Date".

I also set up the code to ignore non-date entries such as text.

Make sure that you try this code in a backup copy of your workbook. Since macros can not be undone, any changes made by the code are "permanent".

Right click the sheet tab for the sheet you want this to run in and choose View Code. Paste this code into the window that opens.

Make a change to Column W and then come back and tell us (nicely) what the code does right and what it does wrong. Since I can't see your worksheet from where I'm sitting, I can't be sure that this is really what you want.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myAnswer as String

'Disable Events while the code is running
Application.EnableEvents = False

'Determine if change was to Column 23 (W)
  If Target.Column = 23 Then
  
'If entry was deleted, present MsgBox
   If Target = "" Then
    Myanswer = MsgBox("Is This A Charged Transaction?" & vbCrLf & vbCrLf & _
                "Click Yes for Charged" & "     " & _
                "No for Gratis" & "     " & _
                "Cancel to Reset Date", vbYesNoCancel, "Transaction Type")

'If user clicked Cancel, replace Date in cell, Exit
   If Myanswer = vbCancel Then
       Target = Cells(Target.Row, 47)
       GoTo Done
   End If
   
'If user answered Yes, place Charged in cell and
'System Date in Column 48 (AV), Exit
   If Myanswer = vbYes Then
       Target = "Charged"
       Cells(Target.Row, 48) = Date
       GoTo Done
   End If
  
'If user answered No, place Gratis in cell, Exit
   If Myanswer = vbNo Then
       Target = "Gratis"
       Cells(Target.Row, 48) = Date
       GoTo Done
   End If
 End If
 
'If entry is a Date, then put date in Column 47 (AU)
   If IsDate(Target) Then
     Cells(Target.Row, 47) = Target
   End If
Done:
 End If

'Re-enable Events
 Application.EnableEvents = True
End Sub


Report •

#3
May 9, 2010 at 10:17:56
Thank you - that worked a dream!
I feel so empowered when things come together.

A levy (20) is applied to column 'U' when a date is entered in column 'W'.

Does this mean that the user also enters the value in Column U or is it done automatically? It is done automatically.

The reason I ask is that it might be easier to restrict user changes to Column U instead of trying to capture the event via code. A restriction on the column U would be ok

Clapping hands with glee!!!!!!!!!
Thank you again
John



Report •

Related Solutions

#4
May 9, 2010 at 10:56:46
Are you asking for help with "A restriction on the column U" or can you take care of that by yourself?


Report •

#5
May 9, 2010 at 14:51:37
I think so - Protect the sheet and lock U

As I said the code works.
However, if a date exists in eg. "W45".
If I highlight and delete range "W43:W47" no change occurs at "W45". In fact the code will not execute thereafter on any row in column W.
If the user discovers this then the whole exercise is defeated.
Is there a workaround this?
If too much effort is involved please do not trouble. You have been a great help to date.


Report •

#6
May 9, 2010 at 17:28:13
It was not clear from your original post that the users would be deleting dates from more than one cell at a time.

To start with, I am going to assume that you got an error the first time your deleted more than one date and that you clicked the End box in the error message.

This stops the code from completing and therefore the Application.EnableEvents = True line never executes. If the code doesn't re-enable Events, then no event code will run until you either re-enable events in some other manner or restart Excel.

If the code bombs again, just run this to re-enable events:

Sub ResetEvents()
Application.EnableEvents = True
End Sub

Second, regarding your statement: If the user discovers this then the whole exercise is defeated.

This isn't just true for Column U or W, but also for the code itself. If a user discovers the code, they could just delete it, disable it - or if they knew how - modify it.

Along with locking any cells that you don't want the users to access, you also need to hide and password protect the code.

That said...

Modifying the code to work for multiple cells would be difficult, mainly due to the MsgBox options. If they delete more than one cell, they would need to respond to the MsgBox for each cell or for the group or for whatever you want them to do. Each option would require different code.

An easier option would be to prevent them from selecting more than one cell if any part of that selection includes any cell(s) in Column W.

The code I offered earlier fires whenever a change is made to the worksheet and only continues if the change was in Column W.

This code will fire whenever the user selects any cell or cells in the worksheet.

If the selection includes 1 or more cells in Column W, then it will check to see if more than one cell has been selected.

If more than one cell is selected, and one or more of those cells is in Column W, it will present a MsgBox to the user and then collapse the selection to a single cell to prevent the user working with the selected range.

Paste this code in the same module as the Worksheet_Change code from above and they should work nicely together.

However - and it's a big however...

I see a potential problem:

If the user does an AutoFill in Column W, it will present the "Invalid Selection" MsgBox, but the AutoFill will already have taken place. This in turn will cause an error in the Change macro. I can capture the error and deal with it, but it would be problematic to reset the AutoFilled cells back their original dates. So we still have an issue with a specific type of "multiple selection" in Column W causing a problem - the AutoFill

VBA does have the ability to turn off AutoFill for the entire workbook, but I don't know of a way to turn it off for just one column. In fact, turning off AutoFill turns it off at the Application level, so it can't be used in any workbook during that Excel session.

Anyway, here's the additional code required to prevent the users from selecting multiple cells that include Column W.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Determine if selected cell(s) include Column 23 (W)
 If Not Intersect(Target, Columns(23)) Is Nothing Then
 
'If Yes, Determine if more than 1 cell was selected
  If Target.Cells.Count > 1 Then
  
'If Yes, Present MsgBox and Collapse Selection
  MsgBox "Cells in Column W Must Be Selected Individually" & _
          vbCrLf & vbCrLf & _
          "Your Selection Will Be Reset", , _
          "Invalid Selection"
  Selection.Cells(1).Select
  End If
 End If
End Sub


Report •

#7
May 10, 2010 at 04:57:20
Thank you for spending the time to help me.
In normal operation user would only select a single cell in W to either enter a date or to delete a date to remove the levy.
Multi cell selection in column W is 99.9% unlikely to occur.
It was as a result of my curiosity that I discovered the implication of multi cell selection in column W. Non-the-less it is an eventuality that is now covered Thank you.

I hesitate to ask! Various daily reports are generated by CommandButtons and they include column W. Column W is visible on the users monitor currently. Is it possible to disable 'the additional code required to prevent the users from selecting multiple cells that include Column W.' when these buttons are invoked?

I do have options if it is not possible.
1. disable the additional code required to prevent the users from selecting multiple cells that include Column W. - Favourite but eliminates the 'catch'.
2. to move column W to column AI and amend button codes to exclude column W. The prospect fills me with trepidation - dyslexia looms is M3063 - MB036 or N6033

Can the ResetEvents() be called if automatically when code fails?
I'm sure you have a life other than servicing 'needy wantabe coders' so feel free to reply -"Go get yourself a book and sort it yourself."

Thank you again for your help thus far.


Report •

#8
May 10, 2010 at 05:53:28
Since I don't know what your CommandButtons do, I'm not sure why you would need to disable the multiple cell code when the CommandButtons are used. Is it causing a problem?

Is the code selecting multiple cells? Keep in mnd that rarely do you need to Select cells in VBA to perform an action on them.

As a simple example, many coders write/record this:

Range("A1").Select
Selection.Value = 3

when all that is needed is this:

Range("A1").Value = 3

In order to disable the multiple cell code when the CommandButtons are used, I guess you could do the same thing I did in the MsgBox code:

Use this at the beginning:

'Disable Events while the code is running
Application.EnableEvents = False

and this at the end:

'Re-enable Events
 Application.EnableEvents = True

re: Can the ResetEvents() be called if automatically when code fails?

To re-enable events in case the code crashes, I added

On Error GoTo Done

at the start of the code so that it jumps to

Application.EnableEvents = True

if an error occurs.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myAnswer As String
On Error GoTo Done:
'Disable Events while the code is running
Application.EnableEvents = False

'Determine if change was to Column 23 (W)
  If Target.Column = 23 Then
  
'If entry was deleted, present MsgBox
   If Target = "" Then
    myAnswer = MsgBox("Is This A Charged Transaction?" & vbCrLf & vbCrLf & _
                "Click Yes for Charged" & "     " & _
                "No for Gratis" & "     " & _
                "Cancel to Reset Date", vbYesNoCancel, "Transaction Type")

'If user clicked Cancel, replace Date in cell, Exit
   If myAnswer = vbCancel Then
       Target = Cells(Target.Row, 47)
       GoTo Done
   End If
   
'If user answered Yes, place Charged in cell and
'System Date in Column 48 (AV), Exit
   If myAnswer = vbYes Then
       Target = "Charged"
       Cells(Target.Row, 48) = Date
       GoTo Done
   End If
  
'If user answered No, place Gratis in cell, Exit
   If myAnswer = vbNo Then
       Target = "Gratis"
       Cells(Target.Row, 48) = Date
       GoTo Done
   End If
 End If
 
'If entry is a Date, then put date in Column 47 (AU)
   If IsDate(Target) Then
     Cells(Target.Row, 47) = Target
   End If
Done:
End If
'Re-enable Events
 Application.EnableEvents = True
End Sub


Report •

#9
May 11, 2010 at 05:46:05
Many thanks - It's all good except for

CommandButton and 'Ctrl' +A stop functioning when
'Disable Events while the code is running
Application.EnableEvents = False

is added to the code.
The code contains "Var = newdate" and debug wants 'Option Explicit' included but debug rejects it wherever I put it!


Report •

#10
May 11, 2010 at 07:10:17
If you use Option Explicit you have to make sure you DIM the variable VAR.

Keep in mind that I'm working blind here as far as what your CommandButton code is doing.

Once we enter the realm of multiple macros and CommandButtons and the interaction between macros, things get lot more compicated than what you asked for in your original post.

This may sound harsher than I mean it to be, so don't take it personally -

You started with a fairly straight forward question:

"Whenever a change is made to Column W, get input from the user and put values/dates in certain cells."

Then we needed code to prevent the user from changing multiple cells.

Now we need to make sure that the code offered to address those issues plays well with existing code in the workbook - code I didn't even know existed.

I'm sure you can see how I'm somewhat hampered here.


Report •

#11
May 11, 2010 at 09:04:39
Sorry, no offense taken. I understand your frustration.
Can you please correct the following code. When I use 'Ctrl'+R it works but using the button it stalls at
press = MsgBox("Have you entered the DATE of the Daily Report"
The button worked prior to adding
'Disable Events while the code is running
Application.EnableEvents = False

The full code is
' Keyboard Shortcut: Ctrl+r
'Disable Events while the code is running
Application.EnableEvents = False
Dim Var, newdate
Dim Message, Title, Default
Message = "What date is the Report for?"
Title = "Daily Report Date"
Default = "Enter date as MONTH / DAY / YEAR"
press = MsgBox("Have you entered the DATE of the Daily Report", vbQuestion + vbYesNoCancel, "Print Daily Report")
If press = 2 Then Exit Sub
If press = 7 Then newdate = InputBox(Message, Title, Default): Sheets("Accounts").Select: Sheets("Accounts").Select: Range("m2") = newdate


Report •

Ask Question