DATE SCRIPT / add function col A,

[Nastech]

hi, have a script using in sheet, if can would like to add a couple of items.
for column A: if hit enter on cell in column "A" (per existing script) does
not do anything if entering a period "." in col A; would like to have any
spaces between words to be replaced by Plus "+" sign. Thanks.

also if possible, would like to sheet to calculate (only once / file script
is in /that file name only: "DATA"), when file is opened, else leave as have
set in excel. (for now, generally set to "manual"). script in use is:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < 130 Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
'make column changes:
If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then
Application.EnableEvents = False
'Destination:
With Me.Cells(.Row, "CF")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
'make column changes:
If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then
Application.EnableEvents = False
'Destination
With Me.Cells(.Row, "CG")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

 

[Dave Peterson]

You could add some code like:

If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then
Application.EnableEvents = False
.value = replace(.value, " ","+")
Application.EnableEvents = True
End If

And add this to the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
Application.Calculate
'and just to make sure?????
'application.Calculation = xlCalculationManual
End Sub

 

[Nastech]

Thanks.. works well, even with my 0 programming skills, in case someone else
wants the mix:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < 130 Then Exit Sub
'add "+" to blank spaces col A:
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then
Application.EnableEvents = False
.Value = Replace(.Value, " ", "+")
Application.EnableEvents = True
End If
'make column changes:
If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then
Application.EnableEvents = False
'Destination:
With Me.Cells(.Row, "CF")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
'make column changes:
If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then
Application.EnableEvents = False
'Destination
With Me.Cells(.Row, "CG")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub