Conversion using VBA - Possible?

[smods]

Hi all,

Is is possible for me to:

Have a cell set as a number
Enter a number
Have that number multiplied by 500
Convert to a currency cell ??

User enters "12" and then "£6,000" is displayed

Cheers

Chris

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    With Target
        .NumberFormat = "$#,##0.00"
        .Value = .Value * 500
    End With
    Application.EnableEvents = True
End If
End Sub

This is restricted to values typed in column A.

 

[smods]

Thank you VoG.

Is it possible for me to do this on a cell by cell basis as opposed to columns?

Regards

Chris

 

[VoG]

Sure: change the range in red to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:F30")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        .NumberFormat = "$#,##0.00"
        .Value = .Value * 500
    End With
    Application.EnableEvents = True
End If
End Sub

 

[smods]

You my friend are a genius! Thanks

A couple more questions if you don't mind..

How would I change the range for cells that are not together, If I wanted this to happen on cells C5, D10 & E12 for example?

Also my SUM function doesn't seem to be working at the bottom now to add all this up, is it something to do with the code?

Regards

Chris

 

[VoG]

You can use e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5, D10, E12")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        .NumberFormat = "$#,##0.00"
        .Value = .Value * 500
    End With
    Application.EnableEvents = True
End If
End Sub

The code won't prevent formulas from working - the only thing that it (and any other macro) will do is make Undo unavailable.

 

[smods]

Thank you.

My SUM is definately not working though and it is set to Automatic calculation in the options. I can't understand why it's not working