refresh unhide row when chosing from a dropdown list.

Hi all,
i got this problem with my excel spreadsheet. I wanted to hide different row when i select different option from my dropdown list. At start, my B3 cell is empty therefore every row is shown. Thing work fine when i select any of the option(a,b,c,d). However when i changed my option from "a" to "b", row1 is still being hide. What can i do to refresh it?

Please Login or Register  to view this content.

You need to unhide the un-indicated rows.
inserting

Please Login or Register  to view this content.

at the start of your routine will fix things. You might also consider inserting Application.ScreenUpdating=False/True to keep the flicker down.

By the way, why the selection_change event. Wouldn't the change event be called less often.

Originally Posted by VBA Noob

When you enter C in B3 your B3 will then be hidden. May what to select a different entry cell

VBA Noob

sorry for the mistake. let say my dropdown list cell is not within the row which i want to hide/unhide?

If you have change from "a" to "b" the routine is not getting to the Else-Unhide. You have to unhide before re-hiding.

Originally Posted by mikerickson

You need to unhide the un-indicated rows.
inserting

Please Login or Register  to view this content.

at the start of your routine will fix things. You might also consider inserting Application.ScreenUpdating=False/True to keep the flicker down.

By the way, why the selection_change event. Wouldn't the change event be called less often.

Mind telling me where to look for more information regarding Applicating.ScreenUpdating = False/True?

Sorry,very new to excel programming. I am working thru this problem by problem to achieve my required work. The selection should not be change once only but i am scared that user might select the wrong one at first and related rows are being hide when they change from the initial selection to the next selection.

Thanks for the catch. I edited so it will work.

on a spreadsheet the syntax is =AND(condition1,condition2,....,conditionN)
in Visual Basic the syntax is condition1 And condition2.
for example,

Please Login or Register  to view this content.

In VB, Imp, Xor, or Eqv are also logical operators.

Originally Posted by mikerickson

on a spreadsheet the syntax is =AND(condition1,condition2,....,conditionN)
in Visual Basic the syntax is condition1 And condition2.
for example,

Please Login or Register  to view this content.

In VB, Imp, Xor, or Eqv are also logical operators.

Some basically i can put

if a1="Yes" AND a2="No" AND a3="YES" then
sheets("Line").rows("4").EntireRow.hidden = true

and with i can apply the application.Screenupdating property to this too right?

Yes, (I assume you're using loose syntax to emphasise the And).

The ScreenUpdating just keeps the screen from refreshing until it is set back to true. The rows will hide and unhide no matter how it is set. But, in some applications, refreshing the screen slows things down and in this kind of application, it smooths out the flickering as rows appear and disappear.

Hello Everybody,

Thought I would share a condensed version of this code with you. If B3 is the linked cell of the Drop Down., it doesn't matter if it is hidden. Unless you want the user to input a value there.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Originally Posted by mikerickson

Yes, (I assume you're using loose syntax to emphasise the And).

The ScreenUpdating just keeps the screen from refreshing until it is set back to true. The rows will hide and unhide no matter how it is set. But, in some applications, refreshing the screen slows things down and in this kind of application, it smooths out the flickering as rows appear and disappear.

Then how can i better improve in my loose syntax? I would like to correct my syntax while achieving my spreadsheet on the path of learning otherwise it might be harder to correct in the later days. Hoped that you can drop me hint. I hoped to learn more thru discovering it myself.

Please Login or Register  to view this content.

Unless a1, a2, and a3 are string variables this won't work. If you are refering to cells, it would be If Range("a1")="Yes"...

Also, since this comes from user input, and string comparison is case sensitive, If LBound(Range("a1"))="yes"... would be better, so the user can capitilize as they wish.