Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: Converting three calculators in a workbook into one with a dropdown box (hardcoded)

  1. #1
    Seeker TheJay's Avatar
    Join Date
    Oct 2020
    Posts
    15
    Articles
    0
    Excel Version
    2010

    Converting three calculators in a workbook into one with a dropdown box (hardcoded)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Originally posted on Excel Forum: Converting three calculators on a worksheet into one with a dropdown box

    Hello everyone!


    I have attached a copy of my calculator, which manipulates the figure depending on the data input in various cells.


    The user inputs a number in D6, D10 & E10 or D14 & E14 depending on the situation.


    Code:
    C6:D7 for No Payments
    =IF(ISBLANK(D6),"",(C6*D6))
    C10, D10 & E10 for Credit on Account
    =IF(ISBLANK(E10),"",(C10+E10))
    C14, D14 & E14 for Debit on Account
    =IF(ISBLANK(E14),"",(C14*D14-E14))


    I want to merge everything so that the user uses the same boxes for each scenario by choosing whether there are no payments, a credit on the account or a debit on the account from a dropdown box that should appear in B5 with those options.


    This would dictate whether there were three headings or four and the formula/calculation/validation used as mentioned above.
    If the user selected no payments, the headings would be Monthly Amount, Instalments and Total.
    If the user selected credit on account, the headings would be Monthly Amount, Instalments, Credit and Total
    If the user selected debit on account, the headings would be Monthly Amount, Instalments, Debit and Total


    The options must be hardcoded, not referring to external cells. It's close to working, but not quite.


    I am trying to avoid having extra cells and was looking for a hard coded solution as I said before. I've attached my spreadsheet so you can see my progress.


    Mine works fairly well, except for three issues.


    Problems:
    The first is the way that the debit calculator resets itself to hide a row when "No Payments" is selected, particularly now that the Day & Discount calculator has been introduced. Using the reset buttons causes multiple columns to be hidden incorrectly instead of "Credit" in Cell F10.


    The second is the way that the day and discount calculator doesn't hide the other options when a selection is made. I'm trying to replicate the same type of behavior used with the debit calculator for the column, but with the relevant rows instead (obviously now with the same flaw hiding lots of rows).


    The dropdown arrow also seems to move to the second to last row when the option changes from "No Discount". I want it to stay put.


    Spreadsheet:WCD EXPORT.xlsm


    Thank you for your help.

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    272
    Articles
    0
    Excel Version
    2019

  3. #3
    Seeker TheJay's Avatar
    Join Date
    Oct 2020
    Posts
    15
    Articles
    0
    Excel Version
    2010
    No one has replied on mrexcel and I did state where it was originally posted. I also mentioned in my original post on Excel Forum "Now also posted to MrExcel: https://www.mrexcel.com/board/threads/converting-three-calculators-on-a-worksheet-into-one-with-a-dropdown-box.1148769/" and referred to the original post in this thread and the one on mrexcel about the original.

    Basically, if anyone had wanted to know where else this had been posted, they would have seen from visiting the link in my original post on this forum. That links to the original post on the original forum plus there is an additional post with the link to MrExcel. Additionally, there is a link to the original post on the original forum on MrExcel.

    Can anyone help with this please?
    Last edited by TheJay; 2020-11-01 at 10:36 AM.

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,492
    Articles
    0
    Excel Version
    Office 365 Subscription
    Provide the cross-post links. This is NOT Excel Forum - it is a different forum altogether. Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Seeker TheJay's Avatar
    Join Date
    Oct 2020
    Posts
    15
    Articles
    0
    Excel Version
    2010
    Hello Ali, I realise this isn't Excel Forum. If it was, I wouldn't have posted the same thread again (double posting). The reason I referred to my original post on Excel Forum is because you sanctioned me as an administrator of Excel Forum for not making it clear before when I asked for help elsewhere and didn't mention it.

    My post here actually starts with the original source. I have been trying to figure this out since 21st August 2020 and have been very patient. As I haven't to date had anything concrete, I have now posted elsewhere. Each time, I have referred to the original post on Excel Forum.

    https://www.mrexcel.com/board/thread...wn-box.1148769
    http://www.vbaexpress.com/forum/show...ox-(hardcoded)
    https://www.excelguru.ca/forums/show...ox-(hardcoded)

    If I had already received the relevant information, I wouldn't have posted elsewhere so no one would have wasted their time.

    Can anyone help now please?

  6. #6
    Seeker TheJay's Avatar
    Join Date
    Oct 2020
    Posts
    15
    Articles
    0
    Excel Version
    2010
    Please amend my post to include the following links:
    https://www.excelforum.com/excel-pro...pdown-box.html
    https://www.mrexcel.com/board/threads/converting-three-calculators-on-a-worksheet-into-one-with-a-dropdown-box.1148769
    http://www.vbaexpress.com/forum/showthread.php?68008-Converting-three-calculators-in-a-workbook-into-one-with-a-dropdown-box-(hardcoded)
    https://www.excelguru.ca/forums/show...4952#post44952
    https://www.ozgrid.com/forum/index.p...76#post1241076

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    Quote Originally Posted by TheJay View Post
    Originally posted on Excel Forum: Converting three calculators on a worksheet into one with a dropdown box


    I have attached a copy of my calculator, which manipulates the figure depending on the data input in various cells.


    The user inputs a number in D6, D10 & E10 or D14 & E14 depending on the situation.


    Code:
    C6:D7 for No Payments
    =IF(ISBLANK(D6),"",(C6*D6))
    C10, D10 & E10 for Credit on Account
    =IF(ISBLANK(E10),"",(C10+E10))
    C14, D14 & E14 for Debit on Account
    =IF(ISBLANK(E14),"",(C14*D14-E14))

    Is it reasonable to ask if the above verbiage is wrong or is it the attached file that's wrong ?

  8. #8
    Seeker TheJay's Avatar
    Join Date
    Oct 2020
    Posts
    15
    Articles
    0
    Excel Version
    2010
    I think you are asking whether I should have said that the worksheet was attached or linked. It was linked in the original post but can be found attached to this reply. If I could edit my previous posts I would.
    Attached Files Attached Files

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    No that's not what I'm asking.
    Me thinks you are not working with the file you've supplied for us to work with.
    You say... " The user inputs a number in D6, D10 & E10 or D14 & E14 "
    The user would need to unprotect the sheet to enter anything into 3 of those 5 cells.

    To me this indicates something is wrong as the verbiage and sample sheet don't match
    so anyone that's willing to assist will have to alter one or the other.
    I'm asking which one?

  10. #10
    Seeker TheJay's Avatar
    Join Date
    Oct 2020
    Posts
    15
    Articles
    0
    Excel Version
    2010
    Thank you for clarifying, I see what you mean. I must have made multiple changes without making amendments to the description. Thank you for pointing that out. That might explain a lot.

    D6, D10 & E10 or D14 & E14

    Should read:

    C4 & D4, D10, E10 & (depending on dropdown selection) F10, or C16, D16 & E16.

    Disregard the quoted code, that is wrong. The information thereafter is correct.

    Current code in background:

    Code:
    Option Explicit
    
    Private Sub Worksheet_Activate()
    Call Rst
        Range("C4").Select
        With Worksheets("Instalments")
        With ActiveWindow
            .DisplayFormulas = False
            .DisplayHeadings = False
            .DisplayGridlines = False
            .DisplayHorizontalScrollBar = False
            .DisplayVerticalScrollBar = False
        End With
        With Application
            .DisplayFullScreen = True
            .DisplayFormulaBar = False
            .DisplayStatusBar = False
        End With
        With Application
               .CommandBars("Full Screen").Visible = True
            .CommandBars("Worksheet Menu Bar").Enabled = False
            .CommandBars("Standard").Visible = False
            .CommandBars("Formatting").Visible = False
        End With
    End With
    End Sub
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Select Case Target.Address(0, 0)
            Case "G16"
                ActiveSheet.Shapes("CheckBox6").Visible = (Len(Target.Value) > 0)
        End Select
        'Application.EnableEvents = False
        If Target.Address = ("$C$10") Then
            If Target.Value = "No Payments" Then
                Range("F:F").EntireColumn.Hidden = True
            ElseIf Target.Value = "Credit on Account" Then
                Range("F:F").EntireColumn.Hidden = False
            ElseIf Target.Value = "Debit on Account" Then
                Range("F:F").EntireColumn.Hidden = False
            End If
        End If
        If Target.Address = ("$C$20") Then
            If Target.Value = "No Discount" Then
                Range("21:34").EntireRow.Hidden = True
            ElseIf Target.Value = "25% Discount" Then
                Range("21:24").EntireRow.Hidden = False
            ElseIf Target.Value = "50% Discount" Then
                Range("26:29").EntireRow.Hidden = False
            ElseIf Target.Value = "50% Discount & 25% Discount" Then
                Range("31:34").EntireRow.Hidden = False
            End If
        End If
    End Sub
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Const sCELL_TO_SKIP As String = "G15"
        Const sJUMP_TO_CELL As String = "C16"
        Dim rCellToSkip     As Range
        Set rCellToSkip = Me.Range(sCELL_TO_SKIP)
        If Not Intersect(Target, rCellToSkip) Is Nothing Then
            Me.Range(sJUMP_TO_CELL).Activate
        End If
    End Sub
    Attached Files Attached Files
    Last edited by TheJay; 2020-11-02 at 11:37 PM.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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