Sub Worksheet_Change(ByVal Target As Excel.Range) Questions-Private

[Wescotte]

I'm currently using Worksheet_Change() to verify the user has entered
valid data in specific cells. Here is a sample of my code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each Rng In Target
With Rng
' SUDO CODE STARTING HERE
If .Row = Y And .Column = X Then
If Verify .Value for this cell = True Then
Set Cell Formats (Font, Alignment, Borders etc etc)
else
.Value = ""
End if
Set Default Cell Formats (Font, Alignment, Borders etc etc)
End if

End With
Next Rng
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Now, I'd like to know if there is a way to detect specific changes to a
cell. Like if the user does more than just change .Value.. Can I detect
if the user changes various format options and restore to my default
settings? Or do I simply need set the cells
format options every time a cell is updated even if it's just .Value
being changed?

Also, if the user selects multiple rows and deletes them the program
loops this sub for each cell the user is deleting. Is there a simple
way to say If user is deleting rows skip all these checks and just have
the rows removed?

Thanks
Eric

[Bob Phillips]

"Wescotte" <[email protected]> wrote in message
news:[email protected]...
>
> Now, I'd like to know if there is a way to detect specific changes to a
> cell. Like if the user does more than just change .Value.. Can I detect
> if the user changes various format options and restore to my default
> settings? Or do I simply need set the cells
> format options every time a cell is updated even if it's just .Value
> being changed?
>

You could set a variable to the cell on selection, and upon change test if
the properties are the same. But there are a lot of them and it might be
better just to set to your desired state.

> Also, if the user selects multiple rows and deletes them the program
> loops this sub for each cell the user is deleting. Is there a simple
> way to say If user is deleting rows skip all these checks and just have
> the rows removed?

Can't see how.

[Wescotte]

> Also, if the user selects multiple rows and deletes them the program
> loops this sub for each cell the user is deleting. Is there a simple
> way to say If user is deleting rows skip all these checks and just have
> the rows removed?


>> Can't see how.

The reason I need this is when the user deletes quite a few rows at
once it produces a significant lag to complete the check on all the
cells. Also it produces "phantom" cells where they are technically
empty but the scroll bar on the right doesn't adjust correctly. You
could have 5000 rows.. Delete 4999 of them and then save your workbook.
Close it and reopen it and the Excel would still consider the
spreadsheet to have 5000 rows instead of just 1.

So now the user has a scroll bar that is inaccurate and when the user
prints the spreadsheet instead of 1 page it'll produce however many are
needed to display 4999 blank cells.

[Bob Phillips]

See http://www.contextures.com/xlfaqApp.html#Unused

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wescotte" <[email protected]> wrote in message
news:[email protected]...
> > Also, if the user selects multiple rows and deletes them the program
> > loops this sub for each cell the user is deleting. Is there a simple
> > way to say If user is deleting rows skip all these checks and just have
> > the rows removed?
>
>
> >> Can't see how.
>
> The reason I need this is when the user deletes quite a few rows at
> once it produces a significant lag to complete the check on all the
> cells. Also it produces "phantom" cells where they are technically
> empty but the scroll bar on the right doesn't adjust correctly. You
> could have 5000 rows.. Delete 4999 of them and then save your workbook.
> Close it and reopen it and the Excel would still consider the
> spreadsheet to have 5000 rows instead of just 1.
>
> So now the user has a scroll bar that is inaccurate and when the user
> prints the spreadsheet instead of 1 page it'll produce however many are
> needed to display 4999 blank cells.
>