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

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

User · Answer

When asking a user for a response to put into a cell using the InputBox method  there are usually three things that can happen      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  The user clicks Cancel  presses Esc or clicks     Close   The return value is a boolean False  This should be accounted for  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

User · Answer

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

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

User · Answer

When asking a user for a response to put into a cell using the InputBox method  there are usually three things that can happen      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  The user clicks Cancel  presses Esc or clicks     Close   The return value is a boolean False  This should be accounted for  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

[excel] Writing an input integer into a cell

Examples related to excel

Examples related to vba