Using InputBox to clear information in a cell

This is long but interesting and was fun to work on!

Background
So I've been working on a macro that allows me to efficiently preview/edit the sources for a sheet of data by displaying the information listed in a second almost identical sheet that contains only sources, using either a MsgBox or InputBox.

The macro is run by making a selection of either a single cell or a range of cells on the first sheet ('Data') and clicking a button to bring up the appropriate box with the value of the same cell on the second sheet ('Source').

Summary of Macro Function:
What happens when a user selects data?
If selecting a single empty cell, shows a message box that reads please select a cell with data.
If selecting a range of empty cells, shows as above.
If selecting a single cell of data, shows an input box with current source value as default and the ability to edit.
If selecting a range of cells that are all blank except one, treats as if selecting a single cell.
If selecting a range of cells that have multiple values of data, returns message box of sources, no edit capability.
Range of cells skip blank data cells.

The Issue
I noticed that with my current code, if I select a single cell, try to delete the default input box value, and press OK, the action is rendered useless and the current value remains in the 'Sources' Sheet. This is the result of me trying to make the Cancel button not delete the default value.

If I enter a single space, conditional formatting, which runs off ISBLANK, does not highlight the now sourceless Data. This is because the cell is not effectively blank. I believe the solution is the ability to set the Source cell value back to default when input box is empty and OK is clicked.

Do you guys have any thoughts?

Code attached below with a ton of comments! (feel free to point out where I'm stupid, started VBA a little while ago)

:D

Red

Please Login or Register  to view this content.

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

You may use this:

Please Login or Register  to view this content.

You do not need to use vbNullString test also - only StrPtr test.

vbNullString is constant representing a null string - a null pointer. That is not same as empty string ("") but VBA will treat as equal if you compare them. If you will press Cancel on inputbox, null string is returned. If you press OK but do not enter any data, empty string is returned. To make difference between these two we must test if the pointer is null with StrPtr - this will only be true for null string, not empty string. I hope you can understand my meaning because this is not so easy idea!

I do not understand what you want to do. May you please explain exactly? It can be that you only need ws2.Range(ws2save)