Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to prevent users from overstoring data while dragging rows

0

I am in the debugging process of an application that allows users to reorganize the order of menu items by dragging them up and down. The risk is that they will not depress the SHIFT key while doing so and, therefore, overstore data in their destination.

While I have used arrays and a dictionary to control for this, I have neglected to pursue a more simple solution, to prevent users from dragging data unless they are depressing the SHIFT key.

Can anyone offer a solution?

excel
MS Office Version: Microsoft 365
posted by: r_guild (rep: 6) on Thu May 20, 2021 at 2:13 pm
Answer
Discuss

Discussion

Hi Robert. Can't really help today but have you ruled out making your code conditional on Key Down (looking for the Shift modifier)? 
John_Ru (rep: 6142) May 20, '21 at 2:32 pm
Thanks for the idea, John. I'll need to look at context to see if there are instance where the user is entering something in uppercase. I'll check it out and let you know if this idea is a winner.  Thanks
r_guild (rep: 6) May 20, '21 at 2:37 pm
That didn't work, John because the KeyDown event only occurs in MS Forms. I'm using the Selection_Change event to reselect entire rows to both the prevent the user from editing the row content and to assure they can only drag up or down.

Since, I can't stop them from ignoring the Microsoft programmed warning that they are about to overstore data.  I have developed what I believe at this point is bullet proof code for them to restire the over stored items.

However, I have learned from long experience that when we write the code we become blind to other ways users can break it.

That's why I would prefer a more direct solution.
r_guild (rep: 6) May 20, '21 at 3:15 pm
Please share the code you have.
Variatus (rep: 4889) May 20, '21 at 7:55 pm
The code consists  several functions and is too long to paste in.  However, I can summarize the logic. This of course assumes I cannot prevent the user from failing to depress the SHIFT key while dragging rows up or down.

First, I create a dictionary of all items in the list. I use that dictionary to detect missing items in the list range upon the worksheet_Change event.

All missing items are added to a deleted items dictionary and displayed so the user can restore them.

The user can intentionally delete items by drepressing CTRL- or unintentionally delete them by failing to depress the shift key while dragging items up or down the list without depressing the shift key and overstoring other items.

In a simple situation I can write the range to an array after each worksheet_selection_change and write it back upon detecting the user has deleted items by overstoring them.

However, in this case I allow the user to delete items, sort items, and drag them within a category without reflecting that in the item dictionary. If I restore from the dictionary they lose their modifications to the list order.

To resolve this I delete blank rows in the range and add, the overstored items to the deletions dictionary and display them for the user to restore.
r_guild (rep: 6) May 20, '21 at 9:21 pm
Thanks for the effort but I doubt my ability to suggest an improvement without fully understanding what you already have. I would need a workbook with all of the code in it.
Variatus (rep: 4889) May 20, '21 at 11:12 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login