inputbox

[johann]

how can I use an input box in a way so if the user clicks
CANCEL it won't empty out the cell the inputbox sets the
value for?

Thanks,
Johann


Sub ContractMinRange()

Dim Sep As String

'::contract 1
If ActiveCell.FormulaR1C1 = Sheets("CHARTS").Range("A4")
Then
Sep = InputBox("Enter Minimun Range for " & Sheets
("charts").Range("b4"), "Minimum Range", Sheets
("charts").Range("a30"))
Sheets("charts").Range("A30") = Sep
End If

 

[Harald Staff]

Hi Johann

Modify to:

If Sep <>"" Then Sheets("charts").Range("A30") = Sep

 

[Frank Kabel]

Hi
try
If ActiveCell.FormulaR1C1 = Sheets("CHARTS").Range("A4")
Then
Sep = InputBox("Enter Minimun Range for " & Sheets
("charts").Range("b4"), "Minimum Range", Sheets
("charts").Range("a30"))
if Sep<>"" then
Sheets("charts").Range("A30") = Sep
end if
End If

 

[Tom Ogilvy]

Sub ContractMinRange()

Dim Sep As String

'::contract 1
If ActiveCell.FormulaR1C1 = Sheets("CHARTS").Range("A4")
Then
Sep = InputBox("Enter Minimun Range for " & Sheets
("charts").Range("b4"), "Minimum Range", Sheets
("charts").Range("a30"))
if sep <> "" then
Sheets("charts").Range("A30") = Sep
End if
End If

 

[Erin]

Before you set the value of the cell to Sep, Use an if
statement, something like

if sep >0 then
Sheets("charts").Range("A30") = Sep
end if

That way, you are checking the value before you actually
overwrite the value in your chart, and if it is null, then
you aren't changing anything. You may want to use some
permutation of Is Null or something.

Hope this helps

Erin

 

[johann]

hi,

it still empties out the target cell

Any other ideas?

 

[alldreams]

hi,

It still empties out the target sell

Any other ideas?

 

[Tom Ogilvy]

Three people gave you essentially identical suggestions.

apprarently that isn't your real code or something else is clearing the
cell.

If cancel is hit, the VBA inputbox returns an empty string according to
help. Checking for that return value and doing nothing should prevent
clearing out the cell.