CODEWITHSUNDEEP
excelvbainputbox
Piecevcake
Piecevcake

Reputation: 192

Change inputbox default to the current input

I am trying to write a macro which will re-set the inputbox default value to be the last value inputted, so if I type "2" in the inputbox below, the default will change to 2, for the next time the macro is run.

Only until the workbook I am running the macro on is closed, then the original default can be restored

(Excel 2007)

    ROWSDOWN = InputBox("enter no.of rows DOWN to move the cells", "VerticalMove", _
            -1) 'default -1 (=1 row up)

I have tried setting PREV_ROWSDOWN = ROWSDOWN but my attempt (below) doesn't work: the next time I run the macro the inputbox default is 0. The value of PREV_ROWSDOWN (and ROWSDOWN) is lost when the macro ends?

ROWSDOWN = InputBox("enter no.of rows DOWN to move the cells (0=no change, - =move UP)", "VerticalMove", _
            PREV_ROWSDOWN) 'should set default as value entered last time the macro run
        PREV_ROWSDOWN = ROWSDOWN ''NW-not saved after macro finished, default changed to "0"

How can I achieve this?

Upvotes: 2

Views: 1959

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

  1. I recommend not to use full caps variable names for better readability.
  2. I recommend to use the Application.InputBox method instead of just InputBox because there you can specify the Type of the input. So if you set Type:=1 the user can only enter numbers.
  3. Make sure you use Option Explicit to force proper variable declaration. I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

Persistant until workbook closes …

To make your default value persistent until the workbook closes you need to declare it as Static (see Static statement).

Option Explicit

Public Sub Test()
    Static RowsDown As Long  'Static variables will keep the value until next call
    RowsDown = Application.InputBox(Prompt:="enter no.of rows DOWN to move the cells (0=no change, - =move UP)", Title:="VerticalMove", Default:=RowsDown, Type:=1) 
End Sub

Note that if you close and re-open your workbook it will start with beeing 0 again. If you want it to be something different you need to put something like the following right after your Static line:

If RowsDown = 0 Then RowsDown = -1

Persisant for ever …

Variables cannot keep values when workbooks get closed. If you want to make your value persistent even if the workbook closes and re-opens then you need to save it into a (maybe hidden) worksheet's cell. 

Option Explicit

Public Sub Test()
    Dim RowsDown As Long
    RowsDown = Application.InputBox(Prompt:="enter no.of rows DOWN to move the cells (0=no change, - =move UP)", Title:="VerticalMove", Default:=ThisWorkbook.Worksheets("hiddensheet").Range("A1").Value, Type:=1) 
    ThisWorkbook.Worksheets("hiddensheet").Range("A1").Value = RowsDown
End Sub

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

You need to define global variable, where you define macro with input box, such as:

Private lastInput

Sub DefaultForInputBox()
    lastInput = InputBox("Enter some value", "InputBox", lastInput)
End Sub

Upvotes: 0

Related Questions