InputBox Help

[cottage6]

Hi All,
I'm sure this is an easy question for most, except for me! I have an
InputBox that gets a value and then copies the number down. Numbers will
already be in the
column from the prior week, and could be used again. However, if the user
invokes the macro by mistake and then clicks "Cancel" the first entry gets
wiped out. I'm pretty sure I could get around this, but I really want the
correct way of doing this. Enclosed is my ratty code; help would be much
appreciated!

Sub InputIncTon()
Sheets("Credits").Select
Range("G4").Select

ActiveCell = InputBox("What is the INC/TON?", "INC/TON")
If ActiveCell = "" Then GoTo GetMeOuttaHere

With Worksheets("Credits")
.Range("G4:G600").FillDown
End With

GetMeOuttaHere:

End Sub

 

[Jim Thomlinson]

Try this...

Sub InputIncTon()
dim strAnswer as string

strAnswer = InputBox("What is the INC/TON?", "INC/TON")
if stranswer <> "" then
Worksheets("Credits").Range("G4:G600").value = stranswer
end if

End Sub

 

[Rick Rothstein \(MVP - VB\)]

The reason your cell is getting "wiped out" is because you are assigning the
output of the InputBox directly to the ActiveCell (because the Value
property is the default property for a Range). Try it this way...

Sub InputIncTon()
Dim Answer As Variant
Answer = InputBox("What is the INC/TON?", "INC/TON")
If Len(Answer) > 0 Then
With Worksheets("Credits")
.Range("G4").Value = Answer
.Range("G4:G600").FillDown
End With
End If
End Sub

Notice that no sheets or cells have to be selected in order to work with
them and that with proper structuring of the If-Then block the GoTo command
was eliminated. Also, my personal preference to check if a text variable is
empty is to see if its length is 0 rather than doing a string comparison to
the empty string ("")... it is faster to do it this way.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Sub InputIncTon()
Dim Answer As Variant
Answer = InputBox("What is the INC/TON?", "INC/TON")
If Len(Answer) > 0 Then
With Worksheets("Credits")
.Range("G4").Value = Answer
.Range("G4:G600").FillDown
End With
End If
End Sub

Rick

 

[Conan Kelly]

cottage6,

I would assign the results of the input box to a stirng variable, then test
the string variable:



Sub InputIncTon()
dim pstrResponse as string

Sheets("Credits").Select
Range("G4").Select

pstrResponse = InputBox("What is the INC/TON?", "INC/TON")
If pstrResponse = "" Then
exit sub
else
activecell = pstrResponse
end if

With Worksheets("Credits")
.Range("G4:G600").FillDown
End With

End Sub




Then there is no need for "GetMeOuttaHere"

HTH,

Conan

 

[cottage6]

Of course! The solution always looks so easy after it's presented to me line
by line, until the next time I need to come up with something. Thanks for
your help and have a good day.

 

[cottage6]

Conan, thanks for your response! Looks like any of the 3 solutions would
work fine. No need to use GetMeOuttaHere, but it was pretty much how I felt
at the time...

 

[Subodh Adarkar]

Try This

Sub InputIncTon()
Dim IsIniit As String

IsInit = InputBox("What is the INC/TON?", "INC/TON")

' IsEmpty is Boolean, returns True if variable
'is initialised, False if not.

If IsEmpty(IsInit) = False Then
Worksheets("Credits").Range("G4:G600").Value = IsInit
End If

End Sub

Subodh