Converting three calculators on a worksheet into one with a dropdown box

[TheJay]

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.

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.

Spreadshet: WCD EXPORT.xlsm

Thank you for your help.

 

[TheJay]

Anyone?

 

[alansidman]

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

 

[TheJay]

Still no reply.

 

[Fluff]

Please supply links to all sites where you have asked this question.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[TheJay]

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.

Converting three calculators in a workbook into one with a dropdown box (hardcoded) - OzGrid Free Excel/VBA Help Forum

[xpost][/xpost] Originally posted on Excel Forum: Converting three calculators on a worksheet into one with a dropdown box…

www.ozgrid.com

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

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...

www.vbaexpress.com

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

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 &...

www.excelguru.ca

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?

 

[TheJay]

I missed the fact that my original description is inaccurate. Here it is revised:

Hello everyone!

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

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

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

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 C10 with those options. This is mainly working.

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.

Thank you for your help.