Fix InputBox - Returning Empty String

[spurtniq]

Using inputBox to assign multiple variables and then assigning
the info from those variables to cells which I then reference from
another macro.

What I'm finding is that as already noted here in another thread:

"There is another Inputbox function in VBA which returns an empt
string if cancel is pressed."

This is what seems to be happening if I hit either 'Cancel'
or 'Enter'.

What I'd like to do, is afford the user the opportunity to change
not all or lose all, but to be able to change just some of the
information.

Is there a way with multiple variables to change just some of
the variables but not all without erasing previously entere
information by hitting 'cancel' or 'enter'?

Thanx

 

[Tom Ogilvy]

An input box is generally used for one entry. It is unclear how you are
populating several variables with the results of a single inputbox or how
you would adapt this to allow the user to edit a subset of those entries.

 

[spurtniq]

An input box is generally used for one entry. It is unclear how
you are
populating several variables with the results of a single inputbox or
how
you would adapt this to allow the user to edit a subset of those
entries.

--
Regards,
Tom Ogilvy

You are correct. "InputBox" is generally used for one entry.

I never said I was using "an" inputbox for multiple entries.

I said I was using "inputBox" for several entries. Several
inputBoxes. Several entries using the "inputBox" function.

I, perhaps, could have stated my problem more clearly.

I hope this explanation helps to clarify what I meant.

Now having several entries, I'd like to know how, if it's possible,
by use of the "inputBox" function to NOT have an entry erased
by either hitting the cancel button or Enter key.

Thanx.

 

[spurtniq]

Here's the (pseudo)code I'm working with:

Sub Create_Header_Cells_Info()

'Left Header Info
sName_1 = InputBox("Data_1", "Data_1", "")
sName_2 = InputBox("Data_2", "Data_2", "")
sName_3 = InputBox("Data_3", "Data_3", "")

'Center Header Info
sName_4 = InputBox("Data_4", "Data_4", "")

'Right Header Info
sName_5 = InputBox("Data_5", "Data_5", "")

Sheets("Sheet1").Range("IV1").Value = sName_1
Sheets("Sheet1").Range("IV2").Value = sName_2
Sheets("Sheet1").Range("IV3").Value = sName_3
Sheets("Sheet1").Range("IV5").Value = sName_4
Sheets("Sheet1").Range("IV7").Value = sName_5
End Sub

Is there a way to modify this as I've suggested or is there
simply a better way to go about it?

hth

Than

 

[David Coleman]

Hi

In the past, I've achieved this by assigning the current value to a
temporary variable, showing the inputbox and then either re-inserting the
old value (from the temp variable) or inserting the new value. The choice
of which action can be determined by the response from the inputbox - ""
(empty string) indicates "cancel" was clicked....

Hopefully this is of some use

David

An input box is generally used for one entry. It is unclear how
you are
populating several variables with the results of a single inputbox or
how
you would adapt this to allow the user to edit a subset of those
entries.

--
Regards,
Tom Ogilvy

You are correct. "InputBox" is generally used for one entry.

I never said I was using "an" inputbox for multiple entries.

I said I was using "inputBox" for several entries. Several
inputBoxes. Several entries using the "inputBox" function.

I, perhaps, could have stated my problem more clearly.

I hope this explanation helps to clarify what I meant.

Now having several entries, I'd like to know how, if it's possible,
by use of the "inputBox" function to NOT have an entry erased
by either hitting the cancel button or Enter key.

Thanx.

 

[spurtniq]

HI

IN THE PAST, I'VE ACHIEVED THIS BY ASSIGNING THE CURRENT VALUE TO A
TEMPORARY VARIABLE, SHOWING THE INPUTBOX AND THEN EITHER RE-INSERTING
THE
OLD VALUE (FROM THE TEMP VARIABLE) OR INSERTING THE NEW VALUE. THE
CHOICE
OF WHICH ACTION CAN BE DETERMINED BY THE RESPONSE FROM THE INPUTBOX -
\"\"
(EMPTY STRING) INDICATES \"CANCEL\" WAS CLICKED....

HOPEFULLY THIS IS OF SOME USE

DAVID

Of Course!

Duh!

(Feeling stupid)

It's only how most applications check for changes.
In this case, a backup copy of the variables...

Thanx for that, David.

Makes perfect sense.

 

[spurtniq]

Something like this (per David's suggestion)?

Sub Create_Header_Cells_Info()

sName_1_bakup = Sheets("Sheet1").Range("IV1").Value
sName_2_bakup = Sheets("Sheet1").Range("IV1").Value
sName_3_bakup = Sheets("Sheet1").Range("IV1").Value
sName_4_bakup = Sheets("Sheet1").Range("IV1").Value
sName_5_bakup = Sheets("Sheet1").Range("IV1").Value

'Left Header Info
sName_1 = InputBox("Data_1", "Data_1", "")
sName_2 = InputBox("Data_2", "Data_2", "")
sName_3 = InputBox("Data_3", "Data_3", "")

'Center Header Info
sName_4 = InputBox("Data_4", "Data_4", "")

'Right Header Info
sName_5 = InputBox("Data_5", "Data_5", "")

If sName_1 = "" Then
Sheets("Sheet1").Range("IV1").Value = sName_1_bakup
Else
Sheets("Sheet1").Range("IV1").Value = sName_1
End If

If sName_2 = "" Then
Sheets("Sheet1").Range("IV2").Value = sName_2_bakup
Else
Sheets("Sheet1").Range("IV2").Value = sName_2
End If

If sName_3 = "" Then
Sheets("Sheet1").Range("IV3").Value = sName_3_bakup
Else
Sheets("Sheet1").Range("IV3").Value = sName_3
End If

If sName_4 = "" Then
Sheets("Sheet1").Range("IV5").Value = sName_4_bakup
Else
Sheets("Sheet1").Range("IV5").Value = sName_4
End If

If sName_5 = "" Then
Sheets("Sheet1").Range("IV7").Value = sName_5_bakup
Else
Sheets("Sheet1").Range("IV7").Value = sName_5
End If

End Sub

 

[Tom Ogilvy]

Looks like the long way around the block to me. If the user returns "" then
don't update the cell. Why pick up the variable and write back if it never
changes. Did you really mean to pick up all your backup variables from IV1,
then write them to IV1,2,3,5,7?

Seems like you should be using a userform rather than a bunch of input
boxes.

 

[spurtniq]

*Looks like the long way around the block to me. If the user return
"" then
don't update the cell. Why pick up the variable and write back if i
never
changes. Did you really mean to pick up all your backup variable
from IV1,
then write them to IV1,2,3,5,7?

Seems like you should be using a userform rather than a bunch o
input
boxes.
*

The problem I was having was that if I didn't update the cell,
by either "Cancel" or "Enter" then the variable changed to an
empty variable, thereby erasing my original data.

Short of knowing how to not update the variable AND keeping
the previous content in the process, yes, I then mean to pick up
all my backup variables, as David suggested.

I'm new to this.

I haven't figured out userforms yet. They're somewhere down
the list on my long list of things yet to be learned / mastered
regarding Excel.

I'm open to suggestions / examples.

Thanx