Run macro if DV list selection changes

[XL9149]

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

 

[lrobbo314]

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]

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, 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.

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]

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]

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]

No offense, but I don't open files off the internet.

 

[XL9149]

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.