2

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Piecevcake
  • 195
  • 1
  • 12

2 Answers2

2
  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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thank you I will try the static option and apply it to PREV_ROWSDOWN, as I only need it while I am working on 1 book. And I will try to write in that if PREV_ROWSDOWN=0 then the inputbox default is -1, to reset it for the next time :) BTW I find variables in all caps far easier to work with as they are easily distinguishable and stand out from the other code! – Piecevcake May 20 '20 at 08:34
  • @Piecevcake I doubt that you will find many people who share your opinion about ALL CAPS ^^ I would stick to a common practice since if you ever have to work in a team, they probably won't like you ;) – Pᴇʜ May 20 '20 at 09:20
  • Thank you for your good advice :) A point of clarification please- does "until the workbook closes" mean until the workbook containing the macro closes? (Not the workbook the macro is altering) So if the macro is in personal.xlsb, and used on another workbook, The value will stay after the other workbook is closed, until personal.xlsb is closed? – Piecevcake May 20 '20 at 20:39
  • @Piecevcake Yes, exactly like this. – Pᴇʜ May 21 '20 at 10:49
0

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
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • Just for your interest: There is no actual need to define it globally. I would keep it a local variable but make it `Static` (so it preserves the value until next call). Why? Because I think it is a good practice to keep variables as local as possible, so if there is no need to make it global why should we. • But still your solution would work. – Pᴇʜ May 14 '20 at 07:28
  • 1
    @Pᴇʜ Didn't know about `Static` in VBA :) But I see it works differently than in other programming languages. I agree on keeping variables private. But it also depends on software architecture. – Michał Turczyn May 14 '20 at 07:48
  • 1
    Yes, just mentioned it because I think `Static` is rarely used in VBA and most people forget about it. But for some cases it is really useful, because you don't clog everything up with a lot of global variables. I think it is a cool way to keep things more clean and in control of this one procedure (no other procedure can mess up this variable). – Pᴇʜ May 14 '20 at 07:52
  • This looks useful! Can I ask what part of the code makes it global, and how long does it last for? Does it last after the book is closed? – Piecevcake May 20 '20 at 08:44
  • @Piecevcake Place of declaration makes it global, i.e. in module level,not inside any function or sub. No, it won't last after workbook is closed. – Michał Turczyn May 20 '20 at 09:21
  • Sorry for my ignorance- if I put "lastInput" macro in personal.xlsb, then can I set up individual macros (also in personal.xlsb) to use it ad hoc, to embed 1 set value, simply by calling it at the beginning of the individual macros then using ```lastInput``` as the default in the individual macro's inputbox? – Piecevcake May 20 '20 at 20:19
  • @Piecevcake I don't fully understand use case you presented, but I guess so. You can try it on your own. :) – Michał Turczyn May 20 '20 at 20:26