PDA

View Full Version : Update row cells based on listbox cell value



KongUK
10-27-2017, 12:25 AM
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
10-28-2017, 09:09 AM
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
10-28-2017, 02:13 PM
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
10-29-2017, 05:41 AM
See the attachment.

KongUK
10-31-2017, 07:45 AM
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
10-31-2017, 05:38 PM
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
11-01-2017, 12:10 AM
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
11-01-2017, 03:30 AM
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
11-01-2017, 03:33 AM
Restructure the data in the worksheet.

SamT
11-01-2017, 02:05 PM
cols.Range(Cells(1, 2), Cells(1, 27)).Value = 1

Better to restructure the data to better fit code capabilities.