[excel] Writing an input integer into a cell

I am writing a quick application myself - first project, however I am trying to find the VBA code for writing the result of an input string to a named cell in Excel.

For example, a input box asks the question "Which job number would you like to add to the list?"... the user would then enter a reference number such as "FX1234356". The macro then needs to write that information into a cell, which I can then use to finish the macro (basically a search in some data).

I recommend always using a named range (as you have suggested you are doing) because if any columns or rows are added or deleted, the name reference will update, whereas if you hard code the cell reference (eg "H1" as suggested in one of the responses) in VBA, then it will not update and will point to the wrong cell.

So

Range("RefNo") = InputBox("....") 

is safer than

Range("H1") = InputBox("....") 

You can set the value of several cells, too.

Range("Results").Resize(10,3) = arrResults()

where arrResults is an array of at least 10 rows & 3 columns (and can be any type). If you use this, put this

Option Base 1

at the top of the VBA module, otherwise VBA will assume the array starts at 0 and put a blank first row and column in the sheet. This line makes all arrays start at 1 as a default (which may be abnormal in most languages but works well with spreadsheets).

I've done this kind of thing with a form that contains a TextBox.

So if you wanted to put this in say cell H1, then use:

ActiveSheet.Range("H1").Value = txtBoxName.Text

I recommend always using a named range (as you have suggested you are doing) because if any columns or rows are added or deleted, the name reference will update, whereas if you hard code the cell reference (eg "H1" as suggested in one of the responses) in VBA, then it will not update and will point to the wrong cell.

So

Range("RefNo") = InputBox("....") 

is safer than

Range("H1") = InputBox("....") 

You can set the value of several cells, too.

Range("Results").Resize(10,3) = arrResults()

where arrResults is an array of at least 10 rows & 3 columns (and can be any type). If you use this, put this

Option Base 1

at the top of the VBA module, otherwise VBA will assume the array starts at 0 and put a blank first row and column in the sheet. This line makes all arrays start at 1 as a default (which may be abnormal in most languages but works well with spreadsheets).

When asking a user for a response to put into a cell using the InputBox method, there are usually three things that can happen¹.

1. The user types something in and clicks OK. This is what you expect to happen and you will receive input back that can be returned directly to a cell or a declared variable.
2. The user clicks Cancel, presses Esc or clicks × (Close). The return value is a boolean False. This should be accounted for.
3. The user does not type anything in but clicks OK regardless. The return value is a zero-length string.

If you are putting the return value into a cell, your own logic stream will dictate what you want to do about the latter two scenarios. You may want to clear the cell or you may want to leave the cell contents alone. Here is how to handle the various outcomes with a variant type variable and a Select Case statement.

    Dim returnVal As Variant

    returnVal = InputBox(Prompt:="Type a value:", Title:="Test Data")

    'if the user clicked Cancel, Close or Esc the False
    'is translated to the variant as a vbNullString
    Select Case True
        Case Len(returnVal) = 0
            'no value but user clicked OK - clear the target cell
            Range("A2").ClearContents
        Case Else
            'returned a value with OK, save it
            Range("A2") = returnVal
    End Select

¹ _{There is a fourth scenario when a specific type of InputBox method is used. An InputBox can return a formula, cell range error or array. Those are special cases and requires using very specific syntax options. See the supplied link for more.}

I recommend always using a named range (as you have suggested you are doing) because if any columns or rows are added or deleted, the name reference will update, whereas if you hard code the cell reference (eg "H1" as suggested in one of the responses) in VBA, then it will not update and will point to the wrong cell.

So

Range("RefNo") = InputBox("....") 

is safer than

Range("H1") = InputBox("....") 

You can set the value of several cells, too.

Range("Results").Resize(10,3) = arrResults()

where arrResults is an array of at least 10 rows & 3 columns (and can be any type). If you use this, put this

Option Base 1

at the top of the VBA module, otherwise VBA will assume the array starts at 0 and put a blank first row and column in the sheet. This line makes all arrays start at 1 as a default (which may be abnormal in most languages but works well with spreadsheets).

I've done this kind of thing with a form that contains a TextBox.

So if you wanted to put this in say cell H1, then use:

ActiveSheet.Range("H1").Value = txtBoxName.Text

I recommend always using a named range (as you have suggested you are doing) because if any columns or rows are added or deleted, the name reference will update, whereas if you hard code the cell reference (eg "H1" as suggested in one of the responses) in VBA, then it will not update and will point to the wrong cell.

So

Range("RefNo") = InputBox("....") 

is safer than

Range("H1") = InputBox("....") 

You can set the value of several cells, too.

Range("Results").Resize(10,3) = arrResults()

where arrResults is an array of at least 10 rows & 3 columns (and can be any type). If you use this, put this

Option Base 1

at the top of the VBA module, otherwise VBA will assume the array starts at 0 and put a blank first row and column in the sheet. This line makes all arrays start at 1 as a default (which may be abnormal in most languages but works well with spreadsheets).

When asking a user for a response to put into a cell using the InputBox method, there are usually three things that can happen¹.

1. The user types something in and clicks OK. This is what you expect to happen and you will receive input back that can be returned directly to a cell or a declared variable.
2. The user clicks Cancel, presses Esc or clicks × (Close). The return value is a boolean False. This should be accounted for.
3. The user does not type anything in but clicks OK regardless. The return value is a zero-length string.

If you are putting the return value into a cell, your own logic stream will dictate what you want to do about the latter two scenarios. You may want to clear the cell or you may want to leave the cell contents alone. Here is how to handle the various outcomes with a variant type variable and a Select Case statement.

    Dim returnVal As Variant

    returnVal = InputBox(Prompt:="Type a value:", Title:="Test Data")

    'if the user clicked Cancel, Close or Esc the False
    'is translated to the variant as a vbNullString
    Select Case True
        Case Len(returnVal) = 0
            'no value but user clicked OK - clear the target cell
            Range("A2").ClearContents
        Case Else
            'returned a value with OK, save it
            Range("A2") = returnVal
    End Select

¹ _{There is a fourth scenario when a specific type of InputBox method is used. An InputBox can return a formula, cell range error or array. Those are special cases and requires using very specific syntax options. See the supplied link for more.}

I've done this kind of thing with a form that contains a TextBox.

So if you wanted to put this in say cell H1, then use:

ActiveSheet.Range("H1").Value = txtBoxName.Text