+ Reply to Thread
Results 1 to 11 of 11

InputBox data not adding

  1. #1
    Registered User
    Join Date
    01-09-2019
    Location
    Toronto Canada
    MS-Off Ver
    Office 10
    Posts
    30

    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

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 04-16-2020 at 02:13 AM. Reason: Code tags added

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: InputBox data not adding

    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

  3. #3
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: InputBox data not adding

    This is not your answer, but maybe you can playing around with the code

    Please Login or Register  to view this content.
    The code above will add whether the user select the cell then click OK
    or the user does not select the cell then click OK or click Cancel.
    After 8 iteration, the sum value will be put to whatever cell the user select on userResponce

    Please Login or Register  to view this content.
    The code above will show the addition value into whatever cell the user select on userResponce


    That's for your number-1.

    For number-2, it's not clear to me how you define the sheet name as variable.
    As long as I know, the sheet must active first (or must be activated first) - then you can select the range.

    ' 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
    As long as I know, we cannot
    activate a sheet with a code like blue.

    What I do is select/activate the sheet first, then activate the named range :
    Please Login or Register  to view this content.
    Sheet1.Activate 'don't want to keep this line as it is static
    I wonder how you define the sheet name into variable.

  4. #4
    Registered User
    Join Date
    01-09-2019
    Location
    Toronto Canada
    MS-Off Ver
    Office 10
    Posts
    30

    Re: InputBox data not adding

    Thanks for your response Karmapala....
    I tried your code for both Sub test1 and Sub test2 and both fail, I assume because I haven't defined things properly....I am learning, but I'm not quite there yet when using the DIM statements.

    Sub test1 fails when I get to the line:
    If oAdd <> "" Then oSum = oSum + oAdd..........Gives me the error "Run-Time Error13: Type Mismatch

    Sub test 2 fails when I get to the line:
    If oAdd <> "" Then userResponce.Value = userResponce.Value + oAdd...........Gives me the error "Run-Time Error13: Type Mismatch

    I added these lines to the code you provided, but I think I have something wrong.

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

    Your help is most appreciated.

  5. #5
    Registered User
    Join Date
    01-09-2019
    Location
    Toronto Canada
    MS-Off Ver
    Office 10
    Posts
    30

    Re: InputBox data not adding

    Hi Pepe Le Mokko
    As I said, I am new to posting on this forum. I don't quite understand what you are looking for regarding "Tags". Do the tags identify what one is trying to do with their code? I looked at my posted code and it looks the same as when I posted it. You mentioned that you added the Tags, but I don't see them, so I am a bit confused.

    I would appreciated your comments/help with this as I want to do any future posts correctly.

    Thanks
    Gary

  6. #6
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: InputBox data not adding

    Hi Gary,

    Quote Originally Posted by garyboy7000 View Post
    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.
    Last edited by karmapala; 04-16-2020 at 11:13 PM.

  7. #7
    Registered User
    Join Date
    01-09-2019
    Location
    Toronto Canada
    MS-Off Ver
    Office 10
    Posts
    30

    Re: InputBox data not adding

    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
    Attached Images Attached Images
    Last edited by garyboy7000; 04-16-2020 at 11:33 PM. Reason: sending .jpg file of error in code

  8. #8
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: InputBox data not adding

    Have you remove your "Option Explicit" ?

  9. #9
    Registered User
    Join Date
    01-09-2019
    Location
    Toronto Canada
    MS-Off Ver
    Office 10
    Posts
    30

    Re: InputBox data not adding

    Just deleted "Option Explicit" and tried both test1 and test2 now work. Just wondering if by removing Option Explicit will I compromise other code in the spreadsheet?
    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.

    Thanks again
    Gary

  10. #10
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: InputBox data not adding

    Quote Originally Posted by garyboy7000 View Post
    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.

    Please Login or Register  to view this content.
    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

    Please Login or Register  to view this content.
    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.
    Last edited by karmapala; 04-17-2020 at 03:50 AM.

  11. #11
    Registered User
    Join Date
    01-09-2019
    Location
    Toronto Canada
    MS-Off Ver
    Office 10
    Posts
    30

    Re: InputBox data not adding

    Hi Karmapala

    Played with some things for a bit and came up with the following that seems to work.....Thanks for all of your help!

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] MACRO: If user clicks cancel in inputbox,then do nothing. Problem with inputbox appearance
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-05-2015, 04:33 AM
  2. VBA Code for Adding two cells through Inputbox
    By onlyprasad4u in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2014, 03:16 AM
  3. Finding all Text and adding a inputbox to all search
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2014, 02:21 PM
  4. [SOLVED] Help adding inputbox values
    By Ellen 2Excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2014, 04:03 PM
  5. [SOLVED] Trouble adding carrage return/line feed to application.inputbox message
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2012, 10:14 AM
  6. [SOLVED] Adding a field limitation (0-1 only) to a Private sub inputbox prompt.
    By Coachwooten in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2012, 12:10 PM
  7. Inputbox button control + msgbox for empty inputbox
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-30-2009, 12:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1