VBA Excel macro writing an input integer into a cell.

Question

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).

Answer 1

You can use the Range object in VBA to set the value of a named cell, just like any other cell.

Range("C1").Value = Inputbox("Which job number would you like to add to the list?)

Where "C1" is the name of the cell you want to update.

My Excel VBA is a little bit old and crusty, so there may be a better way to do this in newer versions of Excel.

Answer 2

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

Answer 3

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).