Input box default = last input

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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!
 
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
 
Back
Top