Navigation through my spead sheet

mudogs1

New Member
Joined
Jun 14, 2008
Messages
8
I have created a order form for my shirt business. My problem is: how you navigate this order form past the first row. What I mean is that A2 is the starting row and cell, a drop down menu allows you to pick a design, hit tab( i wish it was "enter" instead of tab) and you move to the next drop down, again tab to the next and so on. The problem is at the end of the row (the quantity box) when you hit enter I want the next row to un-hide and start me in cell A3, ( I have hidden rows 3 to 500 for less clutter). This should repeat at the end of each row. Any body have any ideas, I truely am a rookie with excel. Thank you.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

This ought to do it for you:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("D2:D500")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            <SPAN style="color:#007F00">'   Unhide the next row</SPAN><br>                Rows(Target.Row + 1).EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#007F00">'   Activate cell A in the unhidden row</SPAN><br>                Cells(Target.Row + 1, "A").Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Just change D2:D500 to match the quantity column.

To use it right-click the worksheet tab and select View Code, then paste the code in the new window that opens on the right. Hit Alt+Q to exit back to Excel and try it out.

Hope that helps,
 

mudogs1

New Member
Joined
Jun 14, 2008
Messages
8
Thank you Smitty for the reply I must be doing something wrong because it did not do anything. Not sure if this will matter to the code you sent but i have columns B,C,D,F,H,J,K,L hidden they have formulas and part numbers that the customer doesn't need. A,E,G,I have each of the drop down menus in them.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Just change D2:D500 to match the quantity column.

So you'll need to change D2:D500 to M2:M500. ;)
 

mudogs1

New Member
Joined
Jun 14, 2008
Messages
8
I did that but as I said before nothing, now I did copy every thing between "Private Sub" to "End Sub" and pasted all of it in the new window that came up from view code.
 

mudogs1

New Member
Joined
Jun 14, 2008
Messages
8
Smitty I don't know what I did but it is working now thank you thank you thank you you roc
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Hmmm, it works for me, so now it's time for some checking.

Open the VBE (Visual Basic Editor) with ALT+F11. On the left hand side you'll see the Project Explorer (if you don't, hit CTRL+R).

You'll see a tree structure that will show your open workbooks, like VBAProject (Book1). If you expand that you'll see each one of the sheets in your wb. Double-click on the one where you want the hidden rows to be shown. The code should be in there; if it's not then there's the problem, and you'll have to look for the code and move it there. If it is, then feel free to send me a copy of your wb and I'll get it going, as off the top of my head, I don't know what else it might be.

EDIT: sorry I missed your post. Glad you got it going!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top