Paul,
Thanks for your help. i integrated your suggestions into the code and
it works a treat!
Much appreciated.
Nick
On Sep 22, 3:20 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> the problem is that you're using a Selectionchange event.
> this event is triggered WHENEVER you change ANY cell!
>
> it doesn't even have to be related to $B$4 !!
>
> so first of all, you don't really care if they SELECT another cell,
> you only care if they CHANGE cell B4,
>
> so I'd suggest changing the event to Worksheet_Change instead of
> SelectionChange.
>
> Next, the Target variable is being passed to the event.
> This "Target" is a Range Object of the cell that has been changed.
>
> Test to see if the change is to B4 by using:
>
> If (Target.Address = "$B$4") Then
>
> that way, the fact that your macro is changing the values of other
> cells, even though this event is TRIGGERED, it won't
> re-process the changes.
>
> Next, since you're making changes to the cells and you DON'T
> want events to be triggered for these changes, temporarily disable
> the Event processor by using:
>
> Application.EnableEvents = False
>
> don't forget to use:
>
> Application.EnableEvents = True
>
> at the end of you macro.
>
> hope this helps.
>
> Paul
>
> ________________________________
> From: Nick <n...@pryda.com.au>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Monday, September 21, 2009 10:26:43 AM
> Subject: $$Excel-Macros$$ VBA Code for default listbox value
>
> Hello excel guru's,
> i recently joined this group and have picked up quite a bit of useful
> stuff! i do however have a little problem and would be greatly
> appreciated if someone could advise on what may be the cause.
> i have a listbox in cell B4 which has three options: Roof, Floor, Roof
> +Floor. With any one of these options selected, various other cells
> and listboxes (which have their own data validation formulas to only
> list certain values based on which of the B4 options are selected)
> will either show a default value or will have N/A.
> The code i am using goes something like this:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> On Error GoTo exitsub
> If Range("$B$4") = "Roof + Floor" Then
> With Range("$B$4") = "Roof + Floor"
> Range("$B$18").Value = ("Concrete tiles")
> Range("$B$19").Value = ("10mm Plasterboard")
> Range("$B$20").Value = ("Enter RLW")
> Range("$B$21").Value = ("19mm Particleboard")
> Range("$B$22").Value = ("Normal carpet etc")
> Range("$B$23").Value = ("10mm Plasterboard")
> Range("$B$24").Value = ("Enter FLW")
> Range("$B$30").Value = ("0")
> Range("$B$31").Value = ("1.5")
> Range("$B$32").Value = ("1.8")
> Range("$B$34").Value = ("N2")
> End With
> Else
> If Range("$B$4") = "Floor" Then
> With Range("$B$4") = "Floor"
> Range("$B$18").Value = ("N/A")
> Range("$B$19").Value = ("N/A")
> Range("$B$20").Value = ("N/A")
> Range("$B$21").Value = ("19mm Particleboard")
> Range("$B$22").Value = ("Normal carpet etc")
> Range("$B$23").Value = ("10mm Plasterboard")
> Range("$B$24").Value = ("Enter FLW")
> Range("$B$30").Value = ("N/A")
> Range("$B$31").Value = ("1.5")
> Range("$B$32").Value = ("1.8")
> Range("$B$34").Value = ("N/A")
> End With
> Else
> If Range("$B$4") = "Roof" Then
> With Range("$B$4") = "Roof"
> Range("$B$18").Value = ("Concrete tiles")
> Range("$B$19").Value = ("10mm Plasterboard")
> Range("$B$20").Value = ("Enter RLW")
> Range("$B$21").Value = ("N/A")
> Range("$B$22").Value = ("N/A")
> Range("$B$23").Value = ("N/A")
> Range("$B$24").Value = ("N/A")
> Range("$B$30").Value = ("0")
> Range("$B$31").Value = ("N/A")
> Range("$B$32").Value = ("N/A")
> Range("$B$34").Value = ("N2")
> End With
> End If
> End If
> End If
> exitsub:
> End Sub
>
> Now, it is probably not the best way to do what i want, but for the
> most part, it works fine. My only issue is that once a value is
> selected in B4 and the appropriate cells/list box values are shown, i
> can't select any other value in the available list boxes. every time i
> try to pick a different value in a list and click elsewhere, it
> automatically changes back to the default value as per the code.
> Please help. Any suggestion on a solution or a better way to do this
> would be really appreciated.
> Thanks in advance
> Nick
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
