Solved: Code prevents formulas working!

[dgt]

Hi all

I currently use the following code to change the case of text as required in various worksheets.

However, this code prevents me from using any formulas within the columns, which are being used by this code.

[vba]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False

Select Case Sh.CodeName
Case "Sheet4"
For Each cell In Target.Cells
Select Case cell.Column
Case 4, 5, 7, 9, 10, 14, 16, 17
cell.Value = StrConv(cell.Text, vbProperCase)
If cell.Column = 5 Then
Call ConvertCase1(cell)
End If
Case 11, 18, 21
cell.Value = StrConv(cell.Text, vbUpperCase)
End Select
Next cell

Case "Issues"
For Each cell In Target.Cells
Select Case cell.Column
Case 5, 14
cell.Value = StrConv(cell.Text, vbUpperCase)
End Select
Next cell

Case "Sheet7"
For Each cell In Target.Cells
Select Case cell.Column
Case 3, 6
cell.Value = StrConv(cell.Text, vbUpperCase)
End Select
Next cell

End Select
Application.EnableEvents = True
End Sub
[/vba]

Is it possible to amend the code to allow formulas to be used within the column.

TIA ...David

[IBihy]

Hello David,
from my understanding, it is exactly as you say, your code prevents formulas from working. Have a look at the cpearson[dot]com site, under excel/Events.aspx, it's in the lower third of the page. Sorry for the weird syntax, this site prevents me from posting links.
As I understand it, Application.EnableEvents = False appears to be the killer. On the above named web site there is also sample coding on how to allow worksheet events even though events where blocked via the application object.
If I get it right, formulas are changes to the worksheet ... so worksheet changes should be allowed.

Please let me know if it helped.

Have a nice day.
Isabella

[dgt]

Hi Isabella

Thanks for that tip; unfortunately it made no difference whether the Application.EnableEvents was included or excluded in the code.

I believe that I read somewhere that there is a special piece of code for allowing or ignoring formulas that can be added but I have not been able to find it again.

Kindest regards ...David

[Paul_Hossler]

2 ways come to mind

1.
[VBA]
Case "Sheet4"
For Each cell In Target.Cells
If Not cell.HasFormula Then
Select Case cell.Column
[/VBA]


2. or

[VBA]
For Each cell In Target.SpecialCells(xlCellTypeConstants, xlText).Cells


[/VBA]

Paul

[dgt]

Hi Paul

Tried the 2nd option first because I had been playing around with something similar this afternoon.

When I first tried:

[VBA]
For Each cell In Target.SpecialCells(xlCellTypeConstants, xlText).Cells

[/VBA]

It came up with a class error but when comparing it to my efforts, I found that changing 'xlText' to 'xlTextValues' cured that error. I was able to put formulas in Column 3 but when I selected an empty cell, Excell seemed to go into an endless loop and I had to use the escape key to get out of it.

Tried the first option, which I found needed the addition of an 'End If' statement, well that seemed to get rid of the error and the first time that I experimented with the code, it seemed to work fine. However after I closed and opened the workbook again, things did not work properly.

Now when I enter a formula, it just remains as typed e.g. =J2 does'nt return the value in J2, it just stays as =J2.

Any ideas on how to rectify these problems ...David

[Paul_Hossler]

Sorry - I only put in code fragments.

Excel has some logic that gets kicked in if there's only a single cell in the selection.

Acts like the entire sheet is selected, so might just have been a very long loop.

You might try testing for a one cell selection ...

[vba]
If Target.Cells.Count > 1 then
For Each cell In Target.SpecialCells(xlCellTypeConstants, xlTextvalues).Cells

Else

[/vba]


Post a small sample workbook if you want

Paul

[dgt]

Hi Paul

Just picked up your latest amendment but still having problems. I have only applied it to one worksheet for the moment but it won't compile. It keeps coming up with 'Else without If' error message.

[VBA]
Case "Sheet7"
'For Each cell In Target.Cells
If Target.Cells.Count > 1 Then
For Each cell In Target.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
Else
Select Case cell.Column
Case 3, 6
cell.Value = StrConv(cell.Text, vbUpperCase)

End Select
[/VBA]

Not sure where I'm going wrong though. Have tried adding an 'End If' before 'End Select' but that did'nt work either.

David

[Paul_Hossler]

My bad. This is a more complete answer

[vba]
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
Dim rdata As Range

Application.EnableEvents = False


'MsgBox Target.Address

Select Case Sh.CodeName
Case "Sheet4"
For Each cell In Target.Cells
If Not cell.HasFormula Then
Select Case cell.Column
Case 4, 5, 7, 9, 10, 14, 16, 17
cell.Value = StrConv(cell.Text, vbProperCase)
If cell.Column = 5 Then
Call ConvertCase1(cell)
End If
Case 11, 18, 21
cell.Value = StrConv(cell.Text, vbUpperCase)
End Select
End If
Next cell

Case "Issues"
For Each cell In Target.Cells
If Not cell.HasFormula Then
Select Case cell.Column
Case 5, 14
cell.Value = StrConv(cell.Text, vbUpperCase)
End Select
End If
Next cell
Case "Sheet7"
For Each cell In Target.Cells
If Not cell.HasFormula Then
Select Case cell.Column
Case 3, 6
cell.Value = StrConv(cell.Text, vbUpperCase)
End Select
End If
Next cell
End Select
Application.EnableEvents = True
End Sub
Sub ConvertCase1(r As Range)
End Sub
[/vba]

See if this gets you closer to what you're looking to do

Paul

[dgt]

Hi Paul

Thanks for that fuller piece of code. I took out the 'Option Explicit' as this seemed to affect my 'Workbook_Open' macro and also the last 'Sub & End' as I guessed you put those in for testing purposes.

Not sure about the ' Dim rdata As Range ' as this does not seem to apply to anything but have left it in for the moment.

After that, it appears to be working fine but will be giving it a more hands on test over the next couple of days.

Thanks again ...David

[Paul_Hossler]

'Option Explicit' is used to require all variables to be Dim-ed.

I usually recommend it since it can prevent hard to trace typos in the code, such as


[VBA]

MyLong = 1234


and later

MyOtherLong = 2 * MyLoong
[/VBA]

Without Option Explicit, MyOtherLong will always = 0, since MyLoong is never changed.

With Option Explicit, MyLoong will generate an error

rdata was a piece of leftover code.

Good luck

Paul

[dgt]

Hi Paul

Sorry for delay but have been very busy lately.

Just to let you know the code is working fine. Had a slight problem with one column in a particular worksheet butthis was due to the column being formatted as text, which much to my surprise stopped the formulas working. I changed the format to 'General' and all is well again.

Regards ...David