Excel VBA OnEvent_Change Problem...

[yumbelie]

Hi,

I posted previously with a problem concerning updating a cells interior when the text in a box was changed (

http://www.tek-tips.com/viewthread.cfm

SQID=437242&SPID=707&page=2) - However, I have a problem: The user needs to be limited to two options - 'Mark Record, No Action', So I used a data validation box on my spreadsheet - problem is, when I change the value in the cells it fails to update the cell interior *UNLESS* I click into the cell and press return. Whats perplexing is that this does *not* happen if I use the same cell interior marking code on a blank workbook's worksheet, e.g. I can create a data validation box, stick 2 options in, and when I select one option it updates the cells interior *without* me having to click into the cell and press return when I've selected the value. This is really puzzling me and I have absoloutly no idea why it's happening. I've done a 1:1 copy and paste of the code and still it fails to work in my workbook properly:\.

Thanks for any assistance.

Joseph

 

[xlbo]

Sounds like your events are disabled
put this in a module and run it
sub reEnable()
application.enableevents = true
end sub

Rgds
~Geoff~

 

[yumbelie]

Na, still no joy (I tried calling it at the beginning of the Worksheet_Change code as well as the end, still no joy ) - As I say, it's fine if I type the text in, but if I use a validated list - it just doesn't update the cell interiors, but it's going through the Worksheet_Change even okay, because I strategically place some MsgBox() responses in, they pop up no problems, but the cell interiors just don't change, strange stuff

Thanks anyway

Joseph

 

[xlbo]

If it is running the code then I gues a condition must be failing - have you tried stepping thru the code (F8)

Can you post the code you are using in the change event Rgds
~Geoff~

 

[yumbelie]

The code is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)



crow = CurrentRow(Target.Address)'Gets the current row
Dim tstring As String
'Create Address to compare target address against

tstring = Trim(Left(Target.Address, 3)) & Trim(Str(crow))


'Call reEnable

If Target.Address = tstring Then

If Target = "Deactivate Record" Then

Range(Cells(crow, 1), Cells(crow, 13)).Interior.ColorIndex = 15

ElseIf Target = "No Action" Then

Range(Cells(crow, 1), Cells(crow, 13)).Interior.ColorIndex = 0

End If

End If

'Application.EnableEvents = True

End Sub

As you can see, it ideally needs to use a validated list to stop the user entering values other then Reactivate Record and No Action. Hope it provides you some insight

Many Thanks

Joseph

 

[rmikesmith]

Joseph,

The whole point behind Geoff's remarks about events being disabled is that when in that condition, events are not triggered, and therefore, their event handlers (procedures) do not run. From your posted code, I see you are calling Geoff's procedure from within the Worksheet_Change event procedure. If events are disabled then this procedure will never run! You should run Geoff's procedure manually (i.e. in the VBE, place your cursor anywhere inside the reEnable procedure then select Run/Run Macro from the menu.)

May or may not help, but you'll never know until you are sure events are enabled.

Regards,
Mike

 

[xlbo]

Thx Mike
Rgds
~Geoff~

 

[yumbelie]

Still no joy :\ - I've added list validation to a single test row. I initally switched to the VBE, activated Re-Enable (As you suggested) then changed the value (using the combo drop down) in the cell, however the row was not flagged. So, I tried this procedure in reverse order - cell change, then activate value. I even tried F9 (on a wild guess) key to see if it would update the values, nothing :\ - It just seems so strange, logically (as I see, flawed as this might be) it *should* work. It does parse the Change_Event sub function okay, so there is no reason typing the text should be different from selecting the value from a combo box... Oh well - any additional help appericated guys

Joseph

 

[yumbelie]

One other thing I'd like to add, I just added some If statements to the Change_Event to check the status of Application.EnableEvent and print a msg box. Thing is, it seems when the value in the combo box is changed, Change_Event is *NOT* called (this is contrary to what I had previously stated - my mistake, sorry about that) as no message boxes are printed. However, clicking into the cell and pressing return causes the Change_Event to occur and the Enable Event returns true that is TRUE both at the beginning and end of the Event's execution. So for some reason, Change_Event is not occuring when the value of the cell is changed using Combo box...Any ideas ?

 

[xlbo]

Are you using a combobox or Data Validation

A combobox will not set off the change event as it is not part of the sheet - this will only work if you link to a cell

If you are using Data>Validation then there should be no reason why the change event does not fire

as a work round, you could have a cell with a formula - say =len(A1) (where A1 is your combo / cell to be changed)
then use the Calculate event - this could be modded so that it will only run the event if the active cell is A1 / A2 / B1 (depending on how the user exits the cell)

HTH Rgds
~Geoff~

 

[eargo]

Right, not quite sure how I'd go about this. Based on what you've said, I've created a new column - which calculates the length of the data validation box I'm having problems with. I've then added a Worksheet_Calculate Method, which checks the value of the active cell. PRoblem is, how to I call the Worksheet_Change method? It requires a 'ByVal Target As Excel.Range' argument to be sent - which, according to the erros 'is not optional'. So how would I cause the Worksheet_Change method to be invoked? i.e. what would I pass it? Code for Worksheet_Calculate is:

Private Sub Worksheet_Calculate()

If ActiveCell.Value = 17 Then

Call Worksheet_Change

ElseIf ActiveCell.Value = 9 Then

Call Worksheet_Change

End If

End Sub

 

[RobBroekhuis]

You should never have to call worksheet_change manually - if you want the worksheet_calculate to trigger (conditionally) the same code as the worksheet_change event, then call the same (regular) sub from both event handlers. Make sense?
Rob

 

[yumbelie]

Fantastic, never thought of that - works Many thanks!