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
Sub Source()
Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range, cell As Range, cellsdata$, ipbox$
' Flag will help determine if every cell in range is empty
Dim Flag As Boolean
Flag = False
' Variable used to count the number of non-empty cells in a range
Dim MultiCount As Integer
MultiCount = 0
' Save points for worksheet 1 and worksheet 2 values for later
Dim ws1save$, ws2save$
Set ws1 = ActiveSheet
If ws1.Name = "Data" Then Set ws2 = Sheets("Sources")
If ws1.Name = "Sources" Then Set ws2 = Sheets("Data")
Set rng = Selection
If rng.Count > 1 Then
For Each cell In rng
' Will not count empty cell(s) in the range
If Not IsEmpty(ws1.Range(cell.Address)) Then
' Confirm there is a cell that is not empty
Flag = True
' Will add 1 to itself for every cell in the range that is not empty.
' Works so that if you select all empty range except 1 cell, you can edit that cell's source.
MultiCount = MultiCount + 1
' If selection is more than 1 cell, this vector grows itself.
cellsdata = cellsdata & "> [" & ws1.Range(cell.Address) & "] : " & ws2.Range(cell.Address) & _
vbNewLine
' Will be used if MultiCount = 1 to select the single cell's values within the range
ws1save = ws1.Range(cell.Address)
ws2save = ws2.Range(cell.Address)
End If
' Next cell means do the same for the next selection in the rng -- the following cellsdata includes
' the one before it
Next cell
' If a cell was confirmed to not be empty within the range, then
If Flag Then
If MultiCount = 1 Then
' Allows editing of source and shows saved value of ws2 in text box
ipbox = InputBox(cellsdata & vbNewLine & vbNewLine & "_________________________" & _
vbNewLine & "Please enter the correct source for [" & ws1save & "] below.", _
"Source of Data", ws2save)
' If you cancel or leave the box blank, it keeps the original source
If Not ipbox = vbNullString Then ws2save = ipbox
Else
' Creates the message box, adds the data:source information, and informs about editing only
' possible by selecting one cell
MsgBox cellsdata & vbNewLine & "______________________________________" & _
vbNewLine & "To edit, please select one cell at a time.", , "Source of Data"
End If
' If flag was never set to true (range is empty)
Else
' Politely tells you to select a cell with data
MsgBox cellsdata & vbNewLine & "______________________________________" & _
vbNewLine & "Please select a cell with data.", , "Source of Data"
End If
' If selection is not more than 1
Else
' Will not run for an empty cell
If Not IsEmpty(ws1.Range(rng.Address)) Then
' Allows inputbox to show for only one data:source
cellsdata = "> [" & ws1.Range(rng.Address) & "] : " & ws2.Range(rng.Address)
' Allows editing of source and shows current value of ws2 in text box
ipbox = InputBox(cellsdata & vbNewLine & vbNewLine & "_________________________" & _
vbNewLine & "Please enter the correct source for [" & ws1.Range(rng.Address) & "] below.", _
"Source of Data", ws2.Range(rng.Address))
' If you cancel or leave the box blank, it keeps the original source
If Not ipbox = vbNullString Then ws2.Range(rng.Address) = ipbox
Else
' Politely tells you to select a cell with data
MsgBox cellsdata & vbNewLine & "______________________________________" & _
vbNewLine & "Please select a cell with data.", , "Source of Data"
End If
End If
End Sub
Bookmarks