Input Box using cell as a reference

mrnacar

Board Regular
Joined
Jan 27, 2010
Messages
184
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.

Code:
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>
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,082
Office Version
365, 2010
Platform
Windows
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.

Code:
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:
Code:
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

Board Regular
Joined
Jan 27, 2010
Messages
184
Maybe something like this:
Code:
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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,522
Messages
5,511,795
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top