Run macro if DV list selection changes

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Good evening all,

By way of introduction, my name is Stuart and I am a new member to this forum. I will post an introductory message after this, but I am unfortunately under some time pressure with this query.

I have a data validation list on the first tab of an Excel Workbook, which allows the user to select a language (English, Español, Francais etc.).

On the second tab, there are five cells that the user must input information in, in order to tailor the spreadsheet to their needs.

If the user selects a language on the first tab, then proceeds to input the five fields of information on the second tab, and then returns to change the language a second time, then I need the macro to run in a way such that it clears those five fields of data on the second worksheet, then updates/recalculates/F9s the workbook.

I don't have any prior experience with macros or VBA.

Thanks in advance.

Stuart
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,087
Office Version
  1. 365
Platform
  1. Windows
What cell is the data validation in on sheet 1? And which cells need to be cleared on sheet 2? Are they named sheet 1 and sheet 2?
 

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi Robbo,

Allow me to hop onto my laptop. I knock up a simple version of my workbook as my actual one is 25MB
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,087
Office Version
  1. 365
Platform
  1. Windows
Well, here is sample code. You can change the cell references and the sheet name to match your workbook.

To insert the code, you need to right click on 'sheet 1' and click on 'view code', then paste the following.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Sheets("Sheet2").Range("A1:A5").ClearContents
End If
End Sub
 

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Robbo,

Saved a copy of my original document, deleted all superfluous sheets to reduce size. Attached the document. Seven tabs remain but the ones of relevance are:
  1. G.1 - tab where the user selects the language
  2. S.1 - tab where the user inputs other data

Background:
This document is a template for presenting financial statements, for use across group companies. Those companies operate in different countries and different languages. I want all group companies to submit financial statements in the exact same format, but of course I need to bridge the language problem.

For example, the user (perhaps an English speaking expatriate finance manager located in overseas business unit) will select language on G.1 e.g. English.
The user will then proceed to S.1 and tailor the document to their specific business, by selecting the options in the DV lists there.
However, if the user then wants to go back and change the language (which is conceivable because his GM may only speak Spanish and he may want to present the financial statements to the GM in Spanish, whilst submitting in English to HQ) then this corrupts the DV lists on S.1 (for reasons that are too detailed/complicated to go into here).

Hence if a change of language occurs, the easiest thing to do would be to have a macro delete the data in those fields and have the user re-select those options on S.1 (and the DV lists will operate as normal).



Further thoughts

Note: To clarify, at this stage I only need help with the macro that deletes the S.1 fields. I am not currently looking for anyone to design the below pop-up box feature concept, at this point I simply want to know if such a pop-up box feature is possible.

It's just occurred to me that if the user does indeed change the language, he may not remember to re-populate S.1 after the macro runs. Would it be possible to have a macro to prompt the user to do this?
For example, this may happen by having a macro that, immediately upon the language selection changing, generates a pop up box. That pop-up box would generate a message along the lines of "You have changed the language. You must re-enter your continent" and below this message will be the same DV list where the user can select the continent. After the user has selected the continent, the pop-up box will have an 'OK' button which upon being clicked, feeds that info back to the relevant field in S.1
Once the user has selected the continent, another pop-up box is generated that says "Please re-enter your country", and so on and so forth.

Thanks again
 

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I forgot to attach the example doc, but I've tried to go back and add it and I can't see how to do it. Can you kindly advise?
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,087
Office Version
  1. 365
Platform
  1. Windows
No offense, but I don't open files off the internet.
 

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
No offense, but I don't open files off the internet.

Oh OK. Makes sense. The previous forum I was on (ExcelForum.com) allowed for uploaded files.

But yes I understand why you wouldn't.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,422
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top