Get multiple user selected cell range addresses and write to dictionary

Hello!

I am pretty new to VBA. Here is a rundown on what I am trying to do:

1) On the form, the user will press a button that allows them to select a range. It will open up the "select range" form which the user will highlight what they want on whatever sheet they want and press "apply" and have it save to a string. I want to be able to have a user select multiple ranges across multiple sheets in a workbook (not all at the same time! but one range at a time that falls on different sheets). I want to be able to obtain the address of the cells for every time the user selects a range. They will be able to enter in a name for a range that they select.
2) I want each one of those ranges to be written as the value in a dictionary so that I can retrieve and process them later. The names that the users enter will serve as the key to each of these values.

What is happening:
Everything seems to actually write to the Dictionary. I can iterate through it and it will correctly print out all the keys. If ALL of the user selected cells are on the same sheet, it will actually return those addresses correctly. However, when I change pages (like one selected range is on one sheet and another selected range is on another sheet), then whatever I selected on the latter sheet seems to clear out whatever was selected in the sheet before. The most irritating part was that earlier, if I had different variables for each selected range, it would save correctly and nothing would clear the prior selections. For whatever reason, it does not work this way anymore and even though I have different names for each range, I will still get empty values when I select from different pages.

Here's my code that tries to get the addresses from the selected range:

Private Sub cmdBtn_SelectRange_Click()
currentTableName = BasePayTableSelect.Value
DefsFileGenerator.Hide
Load SelectRangeForm
SelectRangeForm.Caption = "Select Table"
SelectRangeForm.Show (vbModeless)
Do Until SelectRangeForm.Visible = False
DoEvents
Loop
applyCheck = SelectRangeForm.GetApplyStatus
reelName = SelectRangeForm.GetReelName
Unload SelectRangeForm
If applyCheck = True Then
selectionTableRange = ActiveWindow.Selection.Address
reelCollection.Add reelName, selectionTableRange
box_GeneratedReels.AddItem reelName
End If
DefsFileGenerator.Show
End Sub

I have a couple other sections just like this which activated by different buttons that have the range string named different. Like below:


Private Sub cmdBtn_FSSelectRange_Click()
currentTableName = FSPayTableSelect.Value
DefsFileGenerator.Hide
Load SelectRangeForm
SelectRangeForm.Caption = "Select Table"
SelectRangeForm.Show (vbModeless)
Do Until SelectRangeForm.Visible = False
DoEvents
Loop
applyCheck = SelectRangeForm.GetApplyStatus
reelName = SelectRangeForm.GetReelName
Unload SelectRangeForm
If applyCheck = True Then
FSselectionTableRange = ActiveWindow.Selection.Address
reelCollection.Add reelName, FSselectionTableRange
box_GeneratedReels.AddItem reelName
End If
DefsFileGenerator.Show
End Sub

What I'm looking for is a way to make sure that the selections don't clear out the selection before.

Thanks guys!

Nick's picture

not looked at your issue in

not looked at your issue in depth, but have you tried storing the range in a public range object, and using the UNION function to add the ranges together ?

I just tried this method...

I set up a public range object. I would have the selection save to the object like follows:

Set myRange = ActiveWindow.Selection
selectionTableRange = myRange.Address

This still gives me the same problem. Did I implement it correctly?

I didn't implement a UNION because I want each selection to have its own value with its own key. Each value should only span on one page, but the different keys with their associated values might be across different pages. Unless there is another way it can be implemented that I am not sure of?