How to make VBA/Macro & Data Validation list keep/retain the entries I've made without resetting when changing selection from parent cell.

Question

How to make VBA/Macro & Data Validation list keep/retain the entries I've made without resetting when changing selection from parent cell.

Anonymous

Hi,

I've been working to create an Employee Attendance Calendar that uses a data validation drop-down list of months & will show absences to each individual month when selected. I have gotten as far as getting the header dates + day of week to change respectively when selecting a different month, & to calculate the type of absence in the respected 'absence type' column of the employee.

I thought I was all done at first, but after I already made some entries, I went to select February & the data didn't reset for that month. I don't want to clear the table as it would remove entries for January, nor do I want to do the whole copy/paste rigmarole to a new sheet.

So, I was able to figure out how to use a macro to reset the data when I selected a different month, but then when I selected back to January it reset everything again. I can't seem to find an answer for this problem, or I haven't been asking the right question to find the solution.

I appreciate all the help I can get... I've been working on this for more than a week & it's driving me crazy.

Thanks in advance!

Screenshot of the Attendance calendar:

(FYI, just incase you're wondering - the columns in dark red are for holidays, & does NOT have anything to do with the problem).

Showing the Data Validation list of months in cell "A1":

Resets table "H5:AL33" for Feb:

The_Problem... does not keep the entries I made after switching back & forth:

Macro_code usedx:

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

So, since I have no idea where to start with this, what code could I use that would save the values for each individual month when switching back & forth.

I'm not looking for a work around though, I just want to find how to accomplish this by using the correct VBA code.

I need to see your file. Upload in to an Online File Hoster of your choice and post the download link here.

Andreas.

Answer 2

So, I tried using what you suggested for the table.

Sub Test()

Range ("H5:AL33") = ""

End Sub

This takes me back to the first problem I had mentioned. I wouldn't need any VBA at all if I wanted to use this code.

I understand I could change it to multiple sheets without having to use VBA. But since I don't know much about VBA/macro code, I am wanting to figure out how to accomplish this by using VBA code.

So, since I have no idea where to start with this, what code could I use that would save the values for each individual month when switching back & forth.

I'm not looking for a work around though, I just want to find how to accomplish this by using the correct VBA code.

Thank you!

Answer 3

Well, let's simplify it so it becomes obvious.

In a new file, if we write 1,2,3 into the first row...

...and run this code...

Sub Test()

Range("A1:C1") = ""
End Sub

...the values are gone, because your code overwrote them.

If you don't save the values anywhere, you won't be able to restore them, right?

BTW, Range("A1:C1").ClearContents is the correct way to clear the content of cells.

So, for your layout/idea, you need 12 more sheets in which you have to save the data for each month, then you can also restore them depending on which month you select in the drop-down.

This is of course time-consuming, so you can also make one sheet per month and leave the data there. No drop-down, no VBA, just a few formulas, done.

Andreas.

Share via

How to make VBA/Macro & Data Validation list keep/retain the entries I've made without resetting when changing selection from parent cell.

3 answers