VBA

[Jas]

Hello,

I am new to VBA. I know this may be a big ask but please can you explain in laymans language what this code is doing:

Private Sub Worksheet_Change(ByVal Target As Range) 'Prevents users from overwriting formatting

Application.ScreenUpdating = False
Application.EnableEvents = False
strText = Target.Value
Application.Undo
Target = strText
Application.EnableEvents = True


End Sub


Thank you for your time.

 

[Smallman]

Hi Jas

Welcome to Chandoo forum

The coding seems to do nothing. I imagine it is meant to undo any changes to the formatting but it does not achieve that.

The following might be a little easier to read. If you make a change the message box pops up and asks if you want to save the changes.

Private Sub Worksheet_Change(ByVal Target As Range)
  If MsgBox("Undo your last change?", vbYesNo, "Undo") = vbYes Then
     Application.EnableEvents = False
     Application.Undo
     Application.EnableEvents = True
  End If
End Sub

This only relates to the data you enter though.

Take care

Smallman

 

[Jas]

Thank you for responding so quickly.

Would the original code I posted prevent saving of any formulas entered into the cells of the sheet. Because a formula I type displays the result correctly but the formula is not saved.

 

[Smallman]

Hi Jas

No worries. As it stands your coding will overwrite any formula. The vba is saying what ever gets typed in a cell be it a formula or a value then overwrite that with the value of the cell. So if you put a sum formula that = 100 the only thing left will be 100 the sum will disappear.

Take care

Smallman

 

[Jas]

Thank you. That's solved the mystery of why the formula is disappearing. I will try commenting out the code and then enter my formula so that it will be saved and then reinstate the code as intended by the original author, until I get better understanding of VBA.

 

[Smallman]

Hi Jas

You could just change the coding so it allowed the entry of formula. A slight alteration to your code will be all you need.

Private Sub Worksheet_Change(ByVal Target As Range) 'Prevents users from overwriting formatting
Dim strText As String
Application.ScreenUpdating = False
Application.EnableEvents = False
strText = Target.Value
    If Target.HasFormula = True Then
      Application.EnableEvents = True
      Exit Sub
    End If
Application.Undo
Target = strText
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I added a couple of extra lines which should have been in the original code such as the text string declaration and the screenupdating = true.

Take care

Smallman

 

[dan_l]

works fine for me; try fiddling with the path and see what happens.