Update row cells based on listbox cell value

[KongUK]

Hi

I have no clue on this one sorry.
What I want is, I have a column (I) which I have used data validation to create a listbox in the cell (this listbox will be in every row of the column) that has a source from a table with 14 items in the list. That works fine.

I want to know how to setup an on-change event that will update values for other cells in that row e.g. columns (J,K,L) dependant on the listbox selection. The workbook has multiple sheets, this is to work on Sheet1 only.

I tried a few things mentioned in here but nothing ever happened.

Hope that makes sense?

Many thanks

[SamT]

Wouldn't Formulas in j, k, & l Work?

Never mind. A 14 item Formula would be too long, although you could turn the Validation List into a Look up table.

Not being a Formula guy, I immediately thought of writing 3 UDFs and using them in the JKL formulas.

[Simon Lloyd]

You should probably go with writing UDF's as they are much more manageable, here's a very quick "How To...."

When you have written a User Defined Function (UDF) in VBA, the function can be found in the function wizard under the "Custom" category. You can assign a category to the function by changing the "macro properties" of the UDF, however, you will not be able to specify a description for any of the arguments.
So, on to creating our first function, if you aren’t already in the VBE (Visual Basic Editor) then hold down Alt and press each of these keys in turn F11 (gets you to the VBE), I (selects the insert menu), M (selects module). Now you have successfully opened the VBE and inserted a Standard Module we can begin to build a very simple UDF.
Firstly at the top of our new module we should enter “Option Explicit” (without the quotes), this forces us to declare our variables, it will prevent us from incorrectly typing the name of an existing variable, and help keep our code tidy, using Option Explicit is part of a coding convention adopted by professionals – if it’s good enough for them then it’s good enough for us!
Creating a FUNCTION is much like creating a SUB and we start the same using function instead of sub:
N.B Because we will be working with numbers I have set the Data type to Double, that’s my preference as it can house larger numbers, you could use single, integer or long, although you may as well skip integer as execl converts integers to long for calculations anyway!
royUK suggested code change for exchange rate in cell rather than code

Option Explicit

Function Conv(gbp As Double, rte As Double) As Double
'create our calculation
   Conv = gbp * rte
End Function

Now that we have created our currency conversion function we can put it to good use!, press Alt+Q to get back to our worksheet, in B1 enter our new function, it’s done like this: =Conv(A1,1.6) (A1 will be the cell value we are applying our function to), in A1 enter a figure and hit enter, you should now have the value of A1 multiplied by 1.6.
One thing to remember about Functions is that they can only return a value, either to a cell or back to a macro for further use.

But you could use the INDIRECT function in a formula like: =IF(INDIRECT("A2")="January",Sheets2!B1,"") so if my Data validation list is in A2 and I select "January" from the list the contents in my formula cell will show me whats in Sheet 2 cell B1.

[snb]

See the attachment.

[KongUK]

Sorry snb I cannot see what you are doing?

Also I do only need VBA solution as I am inserting new rows in subsequent Sub and do not want to add formatting or formulas at the same time.

I Have some success with this code but cannot get it to use dynamic range? Range required is "I9:I" so start from I9 to last row, as rows will shrink and grow.

Private Sub Worksheet_Change(ByVal Target As Range) ' check if cell in range updated 
    
     Dim p As Integer
     Dim r As Range
     p = Cells(Rows.Count, "A").End(xlUp).Row
     Set r = Range("I9:I" & p)
     
     If Intersect(Activecell, Range("I14")) Is Nothing Then
         'MsgBox "The selected cell is not in the required range", vbCritical + vbOKOnly
         Exit Sub
     End If
          
        If Not Intersect(Target, r) Is Nothing Then
            
            Call ValidateListbox
            
        End If
            
End Sub

And

Sub ValidateListbox()

If Activecell.Value = "NOT STARTED" Then
Range("J14").Value = 1
Range("K14").Value = 1
Range("L14").Value = 1
End If

'This will include other If's for other listbox selections
End Sub

Row 14 used as an example

[SamT]

If Intersect(Activecell, Range("I14")) Is Nothing Then 
         'MsgBox "The selected cell is not in the required range", vbCritical + vbOKOnly
        Exit Sub 
    End If

That means if I14 is NOT the active cell then exit. I think you want to try deleting that snippet.

[KongUK]

I changed this to

If Intersect(Activecell, Range("I9:I" & p)) Is Nothing Then

This just checks for active range, rest of sheet do not want code to run.

This part seems ok but cannot work out how to set range in Sub ValidateListbox to update the current row

If Activecell.Value = "NOT STARTED" Then 
       e.g. Range("ActiveRange").Value = 1

End If

Edit*

I can get it to work by using offset

Activecell.Offset(0, 1).Value = 1
Activecell.Offset(0, 2).Value = 1
Activecell.Offset(0, 3).Value = 1

That will do for now

Thanks

[KongUK]

This works for now as I said but it is a bit slow using multiple offsets

Sub ValidateListbox()
Dim cols As Range
Dim listItem As String
Set cols = Activecell
listItem = Activecell.Value
     
     
    Select Case listItem
         
        Case "NOT STARTED"
              
            With cols
                .Offset(0, 1).Value = 1
                .Offset(0, 3).Value = 1
                .Offset(0, 5).Value = 1
                .Offset(0, 7).Value = 1
                .Offset(0, 9).Value = 1
                .Offset(0, 10).Value = 1
                .Offset(0, 12).Value = 1
                .Offset(0, 13).Value = 1
                .Offset(0, 14).Value = 1
                .Offset(0, 15).Value = 1
                .Offset(0, 18).Value = 1
                .Offset(0, 20).Value = 1
                .Offset(0, 22).Value = 1
                .Offset(0, 24).Value = 1
                .Offset(0, 26).Value = 1
            End With
        
        Case "Next"
        
    End Select
End Sub

Is there a quicker way to do this, I can see each cell being populated although its reasonably fast would prefer instant (to the eye).
I have a matrix table that maybe could be looked up instead of using offset but not sure how that would work or if its faster on performance and the eye?

Case "Next" will be another 14 Cases :-/

Thanks

[snb]

Restructure the data in the worksheet.

[SamT]

cols.Range(Cells(1, 2), Cells(1, 27)).Value = 1

Better to restructure the data to better fit code capabilities.