On_change , Upper case, file SaveAs

[Nigel Stevens]

Please help

I am trying to automate a spreadsheet and have come across
the following problems:

1. I want to enter dates and times in one column,
whenever the corresponding entry is altered in another
column. How do I do this Visual Basic?

2. I want to convert data, on entry, in particular cells
from lower case to upper case. I can use validation rules
to force upper case data entry, but this is not the
complete user-friendly solution. How can I 'format' the
cells so that any data entries are automatically converted
to upper case?

3. And finally, I wish to trap the 'Cancel' and 'No'
button returns from the SaveAs dialog box. The SaveAs
command does not appear to return a response code in the
same waythat the GetSaveAsFilename command does. How do I
get round this.

Many thanks for any light you can shed on these problems.

 

[papou]

Hello Nigel
1) Right click on worksheet tab, View code and paste and amend accordingly
the following example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Target.Offset(, 2).Value = Date
End If
End Sub
2)Right click on worksheet tab, View code and paste and amend accordingly
the following example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Target.Value = Ucase(Target.Value)
End If
End Sub

3) in the following exemplae response will return False if Save As operation
is cancelled
Sub testit()
response = Application.Dialogs(xlDialogSaveAs).Show
MsgBox response
End Sub

HTH
Regards
Pascal

 

[Frank Kabel]

Hi
just some minor additions (also combining 1+2 in one
macro):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
on error goto errhandler
with target
application.enableevents=false
..Offset(, 2).Value = Date
..value=ucase(.value)
end with
End If

errhandler:
application.enableevents=true
End Sub