CODEWITHSUNDEEP
excelvba
MrRBM97
MrRBM97

Reputation: 169

Only Enter Numbers for Decimal with Pre-set Whole-part in Excel

I am trying to find the solution to a very tedious and laborious process. I have to enter thousands of numbers into an excel file, all of the numbers are 7. and then 2 decimal places (i.e. 7.11, 7.56, 7.82, etc...).

I have tried to used cell formating on the row that requires this information by setting the formatting to 7.00, 7.##, 7.??. Although 7.00 kind of works, but I still have to enter the decimal space first, i.e to get "7.33" I have to input ".33", were ideally I just want to enter "33" and it automatically enters "7.33" into the chosen cell.

I know a little about VBA and macros but I am a beginner, so I'm not totally comfortable with most concepts of the language. My understanding is that you have to run the code manually, i.e you have to press a button, but I'm looking for it to run automatically after entering the 2 digits and then pressing enter.

Any help would be appreciated, whether it's the full answer or just pointing me to a subject that I should look at.

Upvotes: 0

Views: 144

Answers (2)

RetiredGeek
RetiredGeek

Reputation: 3158

Give this code a try. Building on Fane's code I added a check for the range of cells where you want this editing to occur. I also added a check to make sure only 2 digits were entered and provided an error message. Also it will return a Number that can be used in calculations.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Cells.Count > 1 Then Exit Sub
  
'Set the range below to the cells you want to use this formatting for!
  If (Application.Intersect(Range("B:B"), Target) Is Nothing) Then Exit Sub
  
  If Target.Value <> 0 Then
    Application.EnableEvents = False
    
    If (Target.Value < 1 Or Target.Value > 99) Then
      MsgBox "Value must be between 1 and 99" & vbCrLf & vbCrLf & _
             "Please correct...", vbOKOnly + vbExclamation, _
             "Input Entry Error:"
      Target.ClearContents
      Exit Sub
    End If
    
    Target.Value = Target.Value / 100 + 7
    Application.EnableEvents = True
    
  End If
  
End Sub 'Worksheet_Change()

HTH

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Try using of sheet Change event to handle the number as you need. Please, copy the next code in the sheet code module:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.cells.count > 1 Then Exit Sub
    If Target.NumberFormat = "7.00" Then
        If Target.Value <> 0 Then
            Application.EnableEvents = False
             Target.Value = Target.Value / 100
            Application.EnableEvents = True
        End If
    End If
End Sub

But is it possible to enter numbers composed from more than 3 digits? If yes, how would you like such a situation to be handled? To warn that it is in illegal number, to reverse at the previous value, to trunk it somehow etc.? So, what should be the way the code should act? This is only up to the operative logic. Otherwise, I think I will be able to handle any of the possibilities, if clear defined... But if not the case, try using the code as it is and send some feedback.

Besides that, the above code deals with cells values added manually or by code, but not for the result of a formula. If this is the case, I can adapt the code in order to use another event, triggered by the formula result changing. But, reading your question with more attention, it looks that it is not the case (" I'm looking for it to run automatically after entering the 2 digits and then pressing enter").

Upvotes: 0

Related Questions