Input box default = last input

[justme]

How can I make the default value of the input box the same as the last user
input (duplicating the functionality of the built-in find/replace dialog)?


Dim iLastRow As Long
Dim i As Long
Dim LastInput As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Dim iLastRow As Long
Dim i As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow

If IsEmpty(Cells(i, "A")) And _
(Cells(i, "F").Value <> "" Or Cells(i, "J").Value <> "") Then
Cells(i, "A").Select
EnterFactory = InputBox("Enter Factory#: ")
If EnterFactory <> "" Then
Cells(i, "A").Value = EnterFactory
End If
End If
Next i

End With
MsgBox "No More Blank Factories"

End Sub

Thank you so much.

 

[steveh]

Save the last value to a cell in a Very Hidden worksheet.

 

[Chip Pearson]

Use code like the following:

Dim DefaultValue As String
Dim Result As String
On Error Resume Next
DefaultValue = ThisWorkbook.Names("InputDefault").RefersTo
DefaultValue = Mid(Replace(DefaultValue, Chr(34), ""), 2)
Result = InputBox(prompt:="Enter something", Default:=DefaultValue)
If Result <> vbNullString Then
ThisWorkbook.Names("InputDefault").Delete
ThisWorkbook.Names.Add Name:="InputDefault", _
RefersTo:=CStr(Result), Visible:=False
End If
On Error GoTo 0


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

 

[justme]

Hi Chip,

Thank you for answering my post. I'm not quite sure how to integrate this in
my code. Do I paste as a separate function, or within my current sub? Thanks
so much!

 

[Tom Ogilvy]

You would integrate it with your code, something like this:

Dim iLastRow As Long
Dim i As Long
Dim LastInput As String


With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow


With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow

If IsEmpty(Cells(i, "A")) And _
(Cells(i, "F").Value <> "" Or _
Cells(i, "J").Value <> "") Then
Cells(i, "A").Select
On Error Resume Next
LastInput = ThisWorkbook.Names("InputDefault").RefersTo
LastInput = Mid(Replace(LastInput, Chr(34), ""), 2)
EnterFactory = InputBox("Enter Factory#: ", _
Default:=LastInput)
If EnterFactory <> "" Then
Cells(i, "A").Value = EnterFactory
ThisWorkbook.Names("InputDefault").Delete
ThisWorkbook.Names.Add Name:="InputDefault", _
RefersTo:=CStr(EnterFactory), Visible:=False
End If
On Error goto 0
End If
Next i

End With
MsgBox "No More Blank Factories"

End Sub

 

[justme]

Thank you, Chip!

 

[justme]

Tom,

You are my hero!

Thank you for all you do!
: ) : ) : )