Private sub worksheet_change

Source: Internet
Author: User

Previously, I added a button to update the dynamic table to trigger the update event, which is concise but not elegant. The boss told me to solve the problem. Thanks to Brother Cui for telling the active event. Later, I found a change event! This seems to be more useful. Cui elder brother discussed how to use the three variables of the company code as the condition for updating the query during the accounting year.

I found two more problems in writing code. The first problem is that there are seven queries in the query. How do you know the last update of the query. This problem is still well solved. I wrote a piece of code to test it, as shown below:

If target. Address = "$ CG $1" or target. Address = "$ Ch $9" then
Msgbox "query 7"
Elseif target. Address = "$ A $1" or target. Address = "$ B $9" then
Msgbox "query 1"
Elseif target. Address = "$ q $1" or target. Address = "$ r$ 9" then
Msgbox "query 2"
Elseif target. Address = "$ AK $1" or target. Address = "$ AK $9" then
Msgbox "query 3"
Elseif target. Address = "$ ax $1" or target. Address = "$ ay $9" then
Msgbox "query 4"
Elseif target. Address = "$ BJ $1" or target. Address = "$ BK $9" then
Msgbox "query 5"
Elseif target. Address = "$ BW $1" or target. Address = "$ BX $9" then
Msgbox "query 6"
End if

Previously, I thought that the last updated query would be the last inserted query, But I found through the code that it was the last update of the first inserted query! The test results are the same each time. Therefore, the cell of the first query is used as the cell to check for updates.

The second problem is not so obvious. The Code will report an error here every time:

'Select the range c_delteplace to be deleted and use the serial number to determine
While worksheets (p_contentsheet). cells (p_conrows, 2). Value "" 'determines whether to delete a blank row.
'Selected and deleted
Worksheets (p_contentsheet). cells (p_conrows, 2). Select
Selection. entirerow. Delete
Wend

I thought I could not encapsulate the code in sub, But I copied the code to the sub of change. After that, I removed selcet and executed it perfectly!

While worksheets (p_contentsheet). cells (p_conrows, 2). Value "" 'determines whether to delete a blank row.
'Selected and deleted
Worksheets (p_contentsheet). cells (p_conrows, 2). entirerow. Delete
Wend

No selcet is added. No wonder the select method of range is invalid. This is the case for an unfamiliar language. If such an error occurs, you must carefully repeat the prompt to solve the problem.

PS: VBA is so powerful that you must read the Excel 2007 programmer reference.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.