InputBox data not adding

1. a) Macro button (located at cell M6 “Click to TOTAL cost of Stock Purchase Amounts”, that adds up various numbers, selected by the user. Firstly, the User will select the cell where the total is to go, and then click on each amount to be added. There is provision for 8 numbers to be added, however, the total only appears if all eight are utilized. If less than 8 are selected (user would click the cancel button to stop entering), but the total would not appear.
b) Secondly, I am unable to use Range Name (TargetSheet) which references a cell holding the sheet name (changes constantly). I am not sure what the error means (COMPILE ERROR – VARIABLE NOT DEFINED) and how to correct it.
Line causing the error - Sheet(TargetSheet).Activate

Sub TotalPurchaseAmounts()

Application.Run "UnProtect"

Dim PurchTotalLoc As Variant
Dim userResponce As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim TargetSheet As Range

On Error Resume Next

Application.DisplayAlerts = False

' Would like to use Range Name "TargetSheet"...but does not work.......COMPILE ERROR - SUB OR FUNCTION NOT DEFINED

' Sheet(TargetSheet).Activate '*****THIS LINE CAUSES THE ERROR

Sheet1.Activate 'don't want to keep this line as it is static

Application.Run "UnProtect"
Set userResponce = Application.InputBox("Please select the cell where you want the Total to appear", _
Title:="Select the cell for your Total", Type:=8)
If userResponce Is Nothing Then Exit Sub

Let PurchTotalLoc = userResponce.Address

On Error Resume Next

'Ask the user for up to eight cells

Set rng1 = Application.InputBox("Click on first cell to be totalled", "First Cell", , , , , , 8)
If rng1 = "" Then GoTo ext:

Set rng2 = Application.InputBox("Click on second cell to be totalled", "Second Cell", , , , , , 8)
If rng2 = "" Then GoTo ext:

Set rng3 = Application.InputBox("Click on third cell to be totalled", "Third Cell", , , , , , 8)
If rng3 = "" Then GoTo ext:

Set rng4 = Application.InputBox("Click on fourth cell to be totalled", "Fourth Cell", , , , , , 8)
If rng4 = "" Then GoTo ext:

Set rng5 = Application.InputBox("Click on fifth cell to be totalled", "Fifth Cell", , , , , , 8)
If rng5 = "" Then GoTo ext:

Set rng6 = Application.InputBox("Click on sixth cell to be totalled", "Sixth Cell", , , , , , 8)
If rng6 = "" Then GoTo ext:

Set rng7 = Application.InputBox("Click on seventh cell to be totalled", "Seventh Cell", , , , , , 8)
If rng7 = "" Then GoTo ext:

Set rng8 = Application.InputBox("Click on eigth cell to be totalled", "Eighth Cell", , , , , , 8)
If rng8 = "" Then GoTo ext:

'Output to a specific user-selected cell (called "PurchTotalLoc") via InputBox

ext:
Range(PurchTotalLoc) = Application.WorksheetFunction.Sum(rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8)

'puts total in if one selects 8 amounts, but not if one enters less than 8 and hits "cancel"

Application.Run "Protect"

End Sub

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I did it for you (once more). Please read forum rules. Thanks

Hi Gary,

Originally Posted by garyboy7000

Dim userResponce As Range
Dim i As Integer
Dim oAdd As Currency
Dim oSum As Currency

have you tried without DIM ?
If not, please try the code without any DIM.

To be honest, except I copy-paste a code from the web which already has the dims,
I almost never declare variable when I write a code, because :
1. I don't know how to write a dim properly
2. I don't write a complex with tons of codes

Thanks.

Hi Karmapala,
I removed all DIM statements and tried both Subs again and get the following error for both:
COMPILE ERROR: VARIABLE NOT DEFINED

When I click on "Debug", it highlights "Set UserResponce"

Added ActiveSheet.UnProtect and ActiveSheet.Protect to beginning and end of code for test 1 ....didn't notice it wasn't right until I had attached picture of code

Gary

Originally Posted by garyboy7000

Just deleted "Option Explicit" and tried both test1 and test2 now work.

Glad to hear that

Just wondering if by removing Option Explicit will I compromise other code in the spreadsheet?

To be honest, I don't know the answer as I said before I rarely do "dim" in my own code,
except I copy a code from the internet which already has the "dim".

When I only have 2 amounts to select/add together,
is there a way to exit sub without having to click the 'Cancel" button 6 more times.
By clicking it once I would like to exit sub at that point.

If something like that, my way is giving the user a message box if he wants to continue to select a value.

Sub test1()
Set userResponce = Application.InputBox("Please select the cell where you want the Total to appear", _
                    Title:="Select the cell for your Total", Type:=8)
                    
oSum = 0

For i = 1 To 8
oAdd = Application.InputBox("Click on the cell to be totalled")
    If oAdd <> "" Then oSum = oSum + oAdd
respond = MsgBox("Do you want to choose another value ?", vbYesNo)
If respond = vbNo Then Exit For
Next i

ActiveSheet.Unprotect
userResponce.Value = oSum
ActiveSheet.Protect

End Sub

When I only have 2 amounts to select/add together

Another way, assuming your data value is like this (in Column T start from row 2, only 3 values)
EXCEL_2020-04-17_15-39-16.png

Sub test1()
Set userResponce = Application.InputBox("Please select the cell where you want the Total to appear", _
                    Title:="Select the cell for your Total", Type:=8)
                    
oSum = 0

n = Range("T2", Range("T2").End(xlDown)).Rows.Count

For i = 1 To n
oAdd = Application.InputBox("Click on first cell to be totalled")
    If oAdd <> "" Then oSum = oSum + oAdd
Next i

ActiveSheet.Unprotect
userResponce.Value = oSum
ActiveSheet.Protect

End Sub

with the code above, it will prompt the user as many times as how many rows which has value.
So, it's not limited to only 8 data values.
Assuming there are 100 rows with data value, and the user only want to add two values, he needs to click cancel 98 times .

I still don't know how you want the user do when he input the cell to be added :
A. He will consecutively choose the cell then finally he want to stop to choose by click the cancel button
B. He maybe choose one cell on the first prompt, then click cancel on the second prompt, then choose another cell on the third prompt, and so on.