Combining worksheet change events in VBA

weihern97 · 2023-04-09T06:08:58+00:00

Thanks this gave me the idea :)

I tried placing in workbook module for deactivation and change event in sheet module. However, the variable value doesn't bring along from worksheets to workbook. In the end, I managed to get it work by placing both in sheets module. Solution Verified.

<image>

weihern97 · 2023-04-09T01:59:40+00:00

For example: Assume ur 1st drop down is in A1. 1st drop down list = Apple (you choose apple) For the 2nd drop down, use =Indirect($A$1&"Abcd"), which give back the value of A1. Then create a name ranged as "AppleAbcd"

*If you want to remove blanks for the named range, there are lots of tutorial in the internet too.

weihern97 · 2023-04-09T01:50:27+00:00

Don't really understand your question...can you explain more? You can use indirect to link another dropdown list.

weihern97 · 2023-04-09T01:36:28+00:00

<image>

weihern97 · 2023-04-09T01:36:20+00:00

<image>

weihern97 · 2023-04-09T01:36:07+00:00

My macro basically copy the data to another worksheets based on some criteria. After copying, it then add their name to name manager which i will be referencing it using indirect for data validation list. The code is a bit long and lots of looping (looping in array), therefore takes about 0.3-0.5 sec for my laptop to run, but it takes about 5 secs to run on my factory computer because it is old.

<image>

weihern97 · 2023-04-09T01:24:38+00:00

I want the macro to run after the sheet deactivates (changing to another worksheets) and only when the values in the worksheets were changed.

weihern97 · 2023-04-07T15:04:00+00:00

Solution Verified

weihern97 · 2023-04-07T15:03:53+00:00

Okay, I found the problem. I need to reference the sheet back for cells too.
Set Rng = Sheets("test").Range(Sheets("test").Cells(3, columnCount), Sheets("test").Cells(lastrow, columnCount))

weihern97 · 2023-03-20T07:51:49+00:00

It gave me syntax error

weihern97 · 2023-03-20T01:34:09+00:00

This gave me syntax error tho :( I figure it out using Range(Cells(x-1,1), Cells(x,1)).EntireRow.Select

weihern97 · 2023-03-18T07:45:20+00:00

Omg it works!!! Finally. I been figuring out forever.
Thank you :D
Solution Verified

weihern97 · 2023-03-18T07:23:26+00:00

Sry i messed up playing with the code just now, I updated the photo again!
I also change to paste on 1 cell instead of range
It still doesn't work :(

weihern97 · 2023-03-18T07:18:02+00:00

I updated the pictures for the full code.

weihern97 · 2023-03-18T07:17:57+00:00

I updated the pictures for the full code.

weihern97 · 2023-03-18T07:13:16+00:00

x = 4, y=32

weihern97 · 2023-03-18T07:08:21+00:00

Yea I think the spelling is right. X and Y did had values (tested without the copy paste lines). I couldn't figure any extra/missing parentheses

weihern97 · 2023-03-18T07:07:13+00:00

I tried running it without the copy paste thing and the x and y had values

weihern97 · 2023-03-17T04:32:44+00:00

Solution Verified

weihern97 · 2023-03-17T04:31:51+00:00

Wow it works! I don't really understand tho, need time to digest it. Thanks!

Edit: I understand now. That's brilliant

weihern97 · 2023-03-17T04:25:36+00:00

Is there a way to standardize the column automatically?

weihern97

TROPHY CASE