PDA

View Full Version : Solved: Input Box help



Emoncada
04-20-2007, 06:17 AM
I have this vb code
Sub Description_New()
' New_Description Macro
Dim res As String, lft As Range
res = InputBox("Please Enter Description")
If res = "" Then
Exit Sub

Else:
ActiveSheet.Unprotect
ActiveCell.Value = res
'----------------------------------------------------------------------
Range("B242:B251").Select
Selection.ClearContents
Range("B242").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]<>"""",""EXPENSE"","""")"
Range("B242").Select
Selection.AutoFill Destination:=Range("B242:B251"), Type:=xlFillDefault
Range("B242:B251").Select
Range("B242").Select
'----------------------------------------------------------------------

With Range("C242:C251").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'----------------------------------------------------------------------
'---------------------------------------------------------------------- End If
End Sub


What I need is possible simplify this while adding another inputbox.
Basically I need to add another inputbox, but don't know how to tell it to go the the left of the cell that "res" is entered.
So if res is activecell in C242 then the other inputbox would go into B242.
Hope that explains what i am trying to go for.

feathers212
04-20-2007, 06:33 AM
You can determine the row and column locations of the active cell:
MsgBox ("Row " & ActiveCell.Row & " and Column " & ActiveCell.Column)
So then.....
Cells(ActiveCell.Row, ActiveCell.Column).Value = res
Cells(ActiveCell.Row, ActiveCell.Column - 1).Value = res2

mdmackillop
04-20-2007, 06:57 AM
Basically I need to add another inputbox, but don't know how to tell it to go the the left of the cell that "res" is entered.

Res could be anywhere on the sheet. Should it always be C242.


So if res is activecell in C242 then the other inputbox would go into B242.

B242 is being filled by "Expenses". You imply something from an inputbox should be entered here. Can you clarify

What is intended with the validation code?

Are your limits always as the hardcoded ranges, or do they need to be flexible?

Can you post a workbook with sample data and a few notes regarding what appears where.

Charlize
04-20-2007, 07:00 AM
ActiveCell.Value = res
ActiveCell.Offset(, -1).Value = res2Charlize

Emoncada
04-20-2007, 07:22 AM
OK I was able to make it work with feathers post. Just wanted to know if I can add something.
Sub Description_New()
' New_Description Macro
Dim res As String, res2 As String, lft As Range
res = InputBox("Please Enter Description")
res2 = InputBox("Is this Item an Expense? Leave Blank to Capture Serial Number!")
If res = "" Then
Exit Sub


Else:
ActiveSheet.Unprotect
'ActiveCell.Value = res
Cells(ActiveCell.Row, ActiveCell.Column).Value = res
If res2 = "" Then
res3 = InputBox("Please Enter Serial Number")
Cells(ActiveCell.Row, ActiveCell.Column - 1).Value = res3
Else
Cells(ActiveCell.Row, ActiveCell.Column - 1).Value = res2
End If
'----------------------------------------------------------------------

With Range("C242:C251").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'----------------------------------------------------------------------

'----------------------------------------------------------------------
End If
End Sub


How can I make all InputBox Data entered go on the sheet in CAPS?

Emoncada
04-20-2007, 07:26 AM
The validation code was removed because it was for something else I used to do with this spreadsheet. sorry for that confusion.

Emoncada
04-20-2007, 07:28 AM
This part of the code
With Range("C242:C251").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'----------------------------------------------------------------------

What it does it removes the validation Cells C242:C251 originally has so that the inputbox data would now replace that.

mdmackillop
04-20-2007, 07:42 AM
How can I make all InputBox Data entered go on the sheet in CAPS? ActiveCell.Offset(,-1).Value = ucase(res3)

mdmackillop
04-20-2007, 07:52 AM
Hi Emoncada
Rather than use multiple inputboxes, if you have more than one item of data to enter, create a simple userform

Emoncada
04-20-2007, 08:39 AM
Perfect mcMack thanks for that Ucase
Thanks Feathers for script also.