VBA: Multiple Cell Selection issues

[ryansm05]

Hi,

The below code enables me to 'click' for multiple selections as the screenshot highlights.

However, as can be seen in cell K5:
- when selecting '4%' and 'a38%" together - '4%' suddenly become '0.04' and this is causing issues for my lookups.

Does anyone know what is causing this issue and perhaps have a solution to fix it?

Furthermore, if there is a way to achieve 1&2 I would be very much grateful:
1) restrict this code to selecting 2 options only
2) when hitting the DEL button the cell defaults to '[%]'

Dim Oldvalue As String
Dim Newvalue As String


On Error GoTo Exitsub
If Target.Row = 5 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Norie]

If the displayed value is 4% then the actual value in the cell is 0.04, if you want the formatted value then use the Text property.

 

[ryansm05]

You can see from cell J5 that it is displayed correctly, though.

It's only when the macro selects the two values in the same cell that this formatting issue arises?

My knowledge of macros isn't advanced enough to fix this - do you know a workaround?

Thanks
Ryan

 

[Norie]

Ryan

In the code you are taking the value in the cell and that value is 0.04.

Like I said, to get the value as it appears use the Text property.

I would post code for that but what you've posted seems incomplete so I'm not sure where/how you would use Text.