VBA run on change wont work?

[Gylle]

Hi all

I did this VBA
and as far as i can see it should work. The VBA screen don't return any errors.
However nothing happen ....

It should run the script when a value is enteret in rage of cells B1 - B11
It should the change alle the letters to lowercase and the first letter in the line to capital.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Range
For Each x In Range("B1:B11")
x.Value = Application.Replace(LCase(x.Value), 1, 1, UCase(Left(x.Value, 1)))
Next
End Sub


I tested the script like this:

Sub Worksheet_Change()
For Each x In Range("B1:B11")
x.Value = Application.Replace(LCase(x.Value), 1, 1, UCase(Left(x.Value, 1)))
Next
End Sub

and then i can call it via the macro ribbon

But I want the routine to run when the range of cells is changed.

What am i missing?

 

[Joe4]

In order for the code to run automatically, it MUST be in the appropriate Sheet module.
One easy way to ensure that is to go to the sheet you wish to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Edtior window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Check to see if entry is made in range B1:B11
    Set rng = Intersect(Target, Range("B1:B11"))
    
'   Exit if not
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells just updated
    Application.EnableEvents = False
    For Each cell In rng
        cell.Value = Application.Replace(LCase(cell.Value), 1, 1, UCase(Left(cell.Value, 1)))
    Next cell
    Application.EnableEvents = True
    
End Sub

 

[Gylle]

Thanks for help

It works.

Just two more quistions.

1: why can't i run this from Personal.xlsb ...
1a: Alternative can i change it to run in a ActiveSheet something like
Set Target = ActiveSheet

2: Can i add font type and size into the code?

 

[Joe4]

1: why can't i run this from Personal.xlsb ... I can run other scripts like that. and yes also automated scripts.

Because "Worksheet_Change" event procedure only work when placed in the particular Sheet module.
For example, if you want it to run against "Sheet2" in "FileA", then the code must appear in the "Sheet2" VBA module in "FileA".
That is the way sheet event procedure code works - it needs to be placed in a very specific place, and it must have a very specific name.

What automated scripts have you placed in your Personal.xlsb" workbook that apply to other workbooks?

1a: Alternative can i change it to run in a ActiveSheet something like
Set Target = ActiveSheet

No, that is now how it works.
Note that "Target" is NOT a variable that you set.
It is the cell that was updated that triggered the code to run.
So, a "Worksheet_Change" event procedure is fired off when a cell is manually updated on the sheet. "Target" just captures that cell that was updated that fired the code (so you can work with it).

2: Can i add font type and size into the code?

Of course.
Just add lines underneath where we are setting the cell value with formatting that you want to apply to that cell.

 

[Gylle]

First up I whould to ‎apologize‎
I had a look for the automated script and it turned out the module was still in the workbook I was talking about my bad

Second thanks for you help, as you might have guessed I'm not a skilled user in VBA.

Can i call the script from let's say any workbook if i have it locatet in personal.xlsb

With something this?

Activesheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1:C11")) Is Nothing Then
call ChangeFirstLetter
End If
End Sub



Personal.xlsb:

Private Sub ChangeFirstLetter()

Dim rng As Range
Dim cell As Range

' Check to see if entry is made in range C1:C11
Set rng = Intersect(Target, Range("C1:C11"))

' Exit if not
If rng Is Nothing Then Exit Sub

' Loop through cells just updated
Application.EnableEvents = False
For Each cell In rng
cell.Value = Application.Replace(LCase(cell.Value), 1, 1, UCase(Left(cell.Value, 1)))
Range("C1:C11").Font.FontStyle = "Regular"
Range("C1:C11").Font.Size = 14
Range("C1:C11").Font.Color = vbBlack
Range("C1:C11").Font.Name = "Arial"
Next cell
Application.EnableEvents = True

End Sub

Sorry if it getting to borring

In short im hoping for a way to have scripts located in one place so it whould be easy to update or modify only one place

 

[Joe4]

Can i call the script from let's say any workbook if i have it locatet in personal.xlsb

That is really an entirely different question, and should be posted in a new thread (especially since I am not certain about the answer to that - I think "No", but I am not 100% confident in that).

 

[Gylle]

Thank you my friend for all oyur help

 

[Joe4]

You are welcome.