VBA Express : Excel - Add Text to Cells Based on User Input

Excel

Add Text to Cells Based on User Input

Ease of Use

Easy

Version tested with

2000 / 2002

Submitted by:

Jacob Hilderbrand

Description:

This macro adds text to a cell based on input box responses from a user.

Discussion:

You want to track data in a cell, but your users may not input it correctly, or Excel formatting may change the value if it is a number. This macro demonstrates how to work around this limitation. The example asks the user how many assignment they have completed, and how many have been assigned, then displays that number in a cell as x / y. Inputting this manually into a cell would cause the fraction to be reduced.

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
     
    Dim Rng1            As Range 
    Dim Prompt          As String 
    Dim Title           As String 
    Dim Completed       As Long 
    Dim Total           As Long 
     
    Set Rng1 = Me.Range("A1:A50") 
    If Intersect(Target(1, 1), Rng1) Is Nothing Then 
        Exit Sub 
    End If 
    Cancel = True 
    On Error Resume Next 
    Prompt = "How many assignments have you completed?" 
    Title = "Completed Assignments" 
    Completed = InputBox(Prompt, Title) 
     
    Prompt = "How many total assignments do you have?" 
    Title = "Total Assignments" 
    Total = InputBox(Prompt, Title) 
     
    If Err <> 0 Then 
        On Error GoTo 0 
        Exit Sub 
    End If 
    On Error GoTo 0 
     
    Target(1, 1).NumberFormat = "@" 
    Target(1, 1).Value = Completed & " / " & Total 
     
End Sub

How to use:

Open Excel.
Right click on the sheet that you want this to work on and select View Code.
Paste the code in the Code Window that opens up.
Change ("A1:A50") to the range you want this code to work on.
Close the VBE (Alt + Q or press the X in the top-right corner).

Test the code:

Double click on one of the cells that is within the specified range.

Sample File:

Input Data.zip 7.4KB

Approved by mdmackillop

This entry has been viewed 228 times.