Input Box using cell as a reference

[mrnacar]

Can the following code be modified to look at a cell value when entering in the inputbox? I have column "J" with unique item numbers and instead of writing the code with the actual item numbers, I want to use the cell as a reference for the "Case". I would really appreciate if someone can help in anyway.

Sub Selectitemnumber()
resp = InputBox("Enter Item Number")
Select Case resp
Case "123": Range("B2,C2,D2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
Case Else: MsgBox "Invalid Item Number"
End Select
End Sub

<TABLE style="WIDTH: 77pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=102 x:str><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 16pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=21>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 61pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=81>

J​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=21 x:num>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>

Item Number​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>

2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

123​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>

3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

456​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>

4​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

789​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>

5​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

101​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=21 x:num>

6​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

121​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>

7​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

132​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>

8​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>

143​

</TD></TR></TBODY></TABLE>

 

[JoeMo]

Can the following code be modified to look at a cell value when entering in the inputbox? I have column "J" with unique item numbers and instead of writing the code with the actual item numbers, I want to use the cell as a reference for the "Case". I would really appreciate if someone can help in anyway.

Sub Selectitemnumber()
resp = InputBox("Enter Item Number")
Select Case resp
Case "123": Range("B2,C2,D2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
Case Else: MsgBox "Invalid Item Number"
End Select
End Sub

<table style="width: 77pt; border-collapse: collapse;" x:str="" width="102" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 16pt;" width="21"><col style="width: 61pt;" width="81"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td style="border: 0.5pt solid windowtext; background-color: silver; width: 16pt; height: 12.75pt;" class="xl22" width="21" height="17">
</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: silver; width: 61pt;" class="xl23" width="81">

J​

</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 15.75pt;" class="xl22" x:num="" height="21">

1​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24">

Item Number​

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 12.75pt;" class="xl22" x:num="" height="17">

2​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

123​

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 12.75pt;" class="xl22" x:num="" height="17">

3​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

456​

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 12.75pt;" class="xl22" x:num="" height="17">

4​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

789​

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 12.75pt;" class="xl22" x:num="" height="17">

5​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

101​

</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 15.75pt;" class="xl22" x:num="" height="21">

6​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

121​

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 12.75pt;" class="xl22" x:num="" height="17">

7​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

132​

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: silver; height: 12.75pt;" class="xl22" x:num="" height="17">

8​

</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl24" x:num="">

143​

</td></tr></tbody></table>

Maybe something like this:

Sub Selectitemnumber()
resp = InputBox("Enter Item Number")
Select Case resp
Case CStr(Range("J2").value): Range("B2,C2,D2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
Case Else: MsgBox "Invalid Item Number"
End Select
End Sub

 

[mrnacar]

Maybe something like this:

Sub Selectitemnumber()
resp = InputBox("Enter Item Number")
Select Case resp
Case CStr(Range("J2").value): Range("B2,C2,D2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
Case Else: MsgBox "Invalid Item Number"
End Select
End Sub

It works! Thank you very much!