Code causes crash

[Newbienew]

So i am having an issue with a code that I was using. It makes everything capitalize on the page no matter where. Bu when I use code now it freezes the workbook and then crashes it. Is there anything I can do about this? I have provided the code that i use below



Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If Not IsEmpty(R) And Not R.HasFormula Then R.Value = UCase(R.Value)
Next R
End Sub

 

[silentwolf]

Hi,
why do you use Worksheet_Change Event? Now everytime you change something in the worksheet the code gets driggerd.
So you should put the code into a module and then run the code and that is it.

 

[Newbienew]

If I am not mistaken, I pasted this code in VBA module and hit the run button and that is it. Is this what you are speaking off?

 

[My Aswer Is This]

This type code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If Not IsEmpty(R) And Not R.HasFormula Then R.Value = UCase(R.Value)
Next R
End Sub

Is not the type code which will run when you click a button.
​

 

[Fluff]

Because you are changing the value in a cell, that triggers the code to run again & again & again in a permanent loop.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim R As Range
   Application.EnableEvents = False
   For Each R In Target
      If Not IsEmpty(R) And Not R.HasFormula Then R.Value = UCase(R.Value)
   Next R
   Application.EnableEvents = True
End Sub

 

[Newbienew]

@My Aswer Is This- Yes it think it was made to do eh loop thing again and again. So I would be able to type and everything would capitalize after I moved to the next cell
@Fluff- I attempted to use your new coding but it would seem it didnt work out for me. I am not sure if i did something wrong. I put it in a module, click run and saved it as a Macro.

The last code i just placed in in the module hit run and that was it worked ever since, until it started crashing excel

 

[My Aswer Is This]

Show us the exact code you last used and which you said crashed Excel.

The code Fluff provided does not go in a Module. It is a sheet event script and is installed like this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The code runs any time you change any value in your worksheet.

 

[Newbienew]

The code i used is in the very first posting. When I change the cell and hit enter excel would freeze and then say not responding then close

 

[My Aswer Is This]

Several here have said you should not use the code in post #1

And you said:

If I am not mistaken, I pasted this code in ​

VBA module and hit the run button ​

and that is it. Is this what you are speaking off?

I said earlier this type code does not go in a Button.

What did not work for you when using Fluff code in post #5

Do not put Fluff code in a button.
Install it like this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The code runs any time you change any value in your worksheet.​

​

 

[Newbienew]

My humblest apologies I did not see several here. I have not made any buttons to use this code. I copy and paste in a module, hit the run button that is in the view code window and close.

The code that Fluffy provided I copied and pasted and did the same thing. This time a window popped up Looking for a name for macro.

My correction to this was the workbook that i was working with was not saved as a macro and when i ran the code this time i close the window without clicking anything.

It worked out for me the second time around Thank you so much for the help and assistance