Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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 GetMeOuttaHe End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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 -- HTH... Jim Thomlinson "cottage6" wrote: 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 GetMeOuttaHe End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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 "cottage6" wrote in message ... 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 GetMeOuttaHe End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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 "cottage6" wrote in message ... 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 GetMeOuttaHe End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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" wrote in message ... 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 GetMeOuttaHe End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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. "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "cottage6" wrote: 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 GetMeOuttaHe End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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... "Conan Kelly" wrote: 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" wrote in message ... 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 GetMeOuttaHe End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox Help
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 "cottage6" wrote in message ... 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. "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "cottage6" wrote: 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 GetMeOuttaHe End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
InputBox with VBA | Excel Discussion (Misc queries) | |||
InputBox | Excel Discussion (Misc queries) | |||
InputBox help please | Excel Programming | |||
inputbox | Excel Discussion (Misc queries) | |||
Inputbox and Application.InputBox | Excel Programming |