Textbox to add a value to the next available cell in a column

[shapz]

I am totally new to macros and need help with a solution.

I want to have a button on a worksheet that when clicked prompts a text box asking for a number. That number should then be pasted to the next available cell in column C on two different sheets.

Any help is greatly appreciated.

 

[Sam_ExcelVault]

Hi Shapz

Paste this code into a new module and then create a button on the first worksheet you'd like the user's number to be inserted into.

Option Explicit

Sub AddUserNumberToColC()


Application.ScreenUpdating = False


Dim userNum As Variant


'Collect user input
userNum = InputBox("Please enter a number...", "Add a number")
    
    'Check that user has entered something into the InputBox
    If userNum = "" Then
        Exit Sub
    Else:
        'Check value is a number
        If Not IsNumeric(userNum) Then
            MsgBox ("You must enter a number")
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End If
 
'Paste number to first worksheet
'Check if cell C2 is empty
    If Range("C2").Value = "" Then
        Range("C2").Value = userNum
    Else:
'Add user number to last cell in column C
    Range("C1").End(xlDown).Offset(1, 0).Value = userNum
    End If
    
'Paste number to second worksheet
    Worksheets("Second Worksheet").Activate
    
'Check if cell C2 is empty
    If Range("C2").Value = "" Then
        Range("C2").Value = userNum
    Else:
'Add user number to last cell in column C
    Range("C1").End(xlDown).Offset(1, 0).Value = userNum
    End If
    
Application.ScreenUpdating = True


End Sub

I've made an assumption that the button that the user will click to run this sub-routine is going to be on the first worksheet that you'd like the user's number to be appended to and that Column C has a header in cell C1. The code above will need to be amended to suit your specific needs where the second worksheet is concerned. Change the 'Worksheets("Second Worksheet").Activate' part of the code to the name of your 2nd worksheet.

There's some basic validation checking in place to ensure the user enters a number rather than anything else. If they don't enter a number the sub-routine exits with a message telling the user that they didn't enter a number.

Hope that helps.