Macro to change list box input range based on selection made in another cell

[Sue]

Ok gurus:
Can someone pleeeeaase shed some light on this for me...

I have been asked to create a form in which the user is asked to
select an appropriate General Ledger (GL) account code [via a list
box], based on the nature of the expense they are processing.
Only issue is that there are several legal entities (companies) within
the organisation, each with their own set of [different] GL account
codes (nothing is ever simple around here!)

So I'm hoping to create a macro(?) where when a particular company is
selected (via a validation list), the input range within the
properties of the list box change to reflect the location of the
corresponding codes; the user then makes their selection and the value
is returned to the relevant cell.

EG:

User selects: Company 1
Account Classification List Box diplays set of GL Codes for Company
1...

User selects: Company 2
Account Classification List Box then diplays set of GL Codes for
Company 2

and so on..

there may be an even simpler way to do this??
Really appreciate any guidance here..

Many thanks in advance
Sue :¬)

 

[Cecilkumara Fernando]

Sue,
You may find this helpful
http://www.contextures.com/xlDataVal02.html#Dynamic
Cecil

 

[Sue]

Hi there

Thanks for your response...the example you forwarded is heading in the
right direction, although the resulting selection is then made via
another validation list as opposed to a dialog/list box, which is what
we're using.

This is what I currently have down for the dialog box itself.:
_________________________

'SHOW DIALOG1 ACC Classification Code
Sub showDialog1()

If ActiveCell.Column = 4 Then
If ActiveCell.Row < 33 Or ActiveCell.Row > 44 Then
resp = MsgBox("Please select the relevant cell in the 'AC
Classification Code' column first.", vbError, "Active Cell Error")
Else
' DialogSheets("ACCCode").ListBoxes("List box 1").ListIndex = 1
resp = DialogSheets("ACCCode").Show
End If
Else
resp = MsgBox("Please select the relevant cell in the 'AC
Classification Code' column first.", vbError, "Active Cell Error")
End If
End Sub

Sub DialogOK1()
ListIndex = DialogSheets("ACCCode").ListBoxes("List box
4").ListIndex
mytext = DialogSheets("ACCCode").ListBoxes("List box
4").List(ListIndex)
ActiveCell.FormulaR1C1 = Trim(Mid$(mytext, 1, 16))

End Sub
______________________________


At present the information within the list contains both the GL Code
and a description of the code, eg:

5100000000006459 Consultancy Costs

However the formula within the macro trims the information so that
only the GL code is returned. The description is required to allow
someone who is unfamiliar with the code (ie: most staff) to make a
more accurate selection.


What I need to do is make the list change based on the selection made
within the target cell..
I think it starts something list this..
_______________________________

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 17 And Target.Column = 6 Then
Select Case Target.Value

[This is where it gets messy - what I want it to do is:
If Case = "TECHNOLOGY" then change source to range "'Sheet1!A:A")]???

Not sure what comes next..
Hope this makes sense..

Cheers again
Sue

Sue,
You may find this helpful
http://www.contextures.com/xlDataVal02.html#Dynamic
Cecil

Ok gurus:
Can someone pleeeeaase shed some light on this for me...

I have been asked to create a form in which the user is asked to
select an appropriate General Ledger (GL) account code [via a list
box], based on the nature of the expense they are processing.
Only issue is that there are several legal entities (companies) within
the organisation, each with their own set of [different] GL account
codes (nothing is ever simple around here!)

So I'm hoping to create a macro(?) where when a particular company is
selected (via a validation list), the input range within the
properties of the list box change to reflect the location of the
corresponding codes; the user then makes their selection and the value
is returned to the relevant cell.

EG:

User selects: Company 1
Account Classification List Box diplays set of GL Codes for Company
1...

User selects: Company 2
Account Classification List Box then diplays set of GL Codes for
Company 2

and so on..

there may be an even simpler way to do this??
Really appreciate any guidance here..

Many thanks in advance
Sue :¬)

 

[Cecilkumara Fernando]

Sue,
Without seen the workbook its bit difficult but as the example you can name
the range you want to get in to the list box exactly to the target cell
value and use the indirect function
say your target cell value is "TECHNOLOGY" then name the source range say
Sheet1A1:A20 as "TECHNOLOGY" and when you use =Indirect(TECHNOLOGY) the
function will get the range Sheet1A1:A20,
You may send a sample workbook to
(e-mail address removed)
regards,
Cecil

Hi there

Thanks for your response...the example you forwarded is heading in the
right direction, although the resulting selection is then made via
another validation list as opposed to a dialog/list box, which is what
we're using.

This is what I currently have down for the dialog box itself.:
_________________________

'SHOW DIALOG1 ACC Classification Code
Sub showDialog1()

If ActiveCell.Column = 4 Then
If ActiveCell.Row < 33 Or ActiveCell.Row > 44 Then
resp = MsgBox("Please select the relevant cell in the 'AC
Classification Code' column first.", vbError, "Active Cell Error")
Else
' DialogSheets("ACCCode").ListBoxes("List box 1").ListIndex = 1
resp = DialogSheets("ACCCode").Show
End If
Else
resp = MsgBox("Please select the relevant cell in the 'AC
Classification Code' column first.", vbError, "Active Cell Error")
End If
End Sub

Sub DialogOK1()
ListIndex = DialogSheets("ACCCode").ListBoxes("List box
4").ListIndex
mytext = DialogSheets("ACCCode").ListBoxes("List box
4").List(ListIndex)
ActiveCell.FormulaR1C1 = Trim(Mid$(mytext, 1, 16))

End Sub
______________________________


At present the information within the list contains both the GL Code
and a description of the code, eg:

5100000000006459 Consultancy Costs

However the formula within the macro trims the information so that
only the GL code is returned. The description is required to allow
someone who is unfamiliar with the code (ie: most staff) to make a
more accurate selection.


What I need to do is make the list change based on the selection made
within the target cell..
I think it starts something list this..
_______________________________

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 17 And Target.Column = 6 Then
Select Case Target.Value

[This is where it gets messy - what I want it to do is:
If Case = "TECHNOLOGY" then change source to range "'Sheet1!A:A")]???

Not sure what comes next..
Hope this makes sense..

Cheers again
Sue



"Cecilkumara Fernando" <cekufdo@sltnetDOTlk> wrote in message

Sue,
You may find this helpful
http://www.contextures.com/xlDataVal02.html#Dynamic
Cecil

Ok gurus:
Can someone pleeeeaase shed some light on this for me...

I have been asked to create a form in which the user is asked to
select an appropriate General Ledger (GL) account code [via a list
box], based on the nature of the expense they are processing.
Only issue is that there are several legal entities (companies) within
the organisation, each with their own set of [different] GL account
codes (nothing is ever simple around here!)

So I'm hoping to create a macro(?) where when a particular company is
selected (via a validation list), the input range within the
properties of the list box change to reflect the location of the
corresponding codes; the user then makes their selection and the value
is returned to the relevant cell.

EG:

User selects: Company 1
Account Classification List Box diplays set of GL Codes for Company
1...

User selects: Company 2
Account Classification List Box then diplays set of GL Codes for
Company 2

and so on..

there may be an even simpler way to do this??
Really appreciate any guidance here..

Many thanks in advance
Sue :¬)