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

TheJay

New member
Joined
Oct 31, 2020
Messages
15
Reaction score
0
Points
0
Excel Version(s)
2010
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:
[COLOR=#1A1A1B][FONT=&quot]C6:D7 for No Payments[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT=&quot]=IF(ISBLANK(D6),"",(C6*D6))
C10, D10 & E10 for Credit on Account[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT=&quot]=IF(ISBLANK(E10),"",(C10+E10))
C14, D14 & E14 for Debit on Account[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT=&quot]=IF(ISBLANK(E14),"",(C14*D14-E14))[/FONT][/COLOR]


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.
 
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:
Provide the cross-post links. This is NOT Excel Forum - it is a different forum altogether. Thanks.
 
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/threa...orksheet-into-one-with-a-dropdown-box.1148769
http://www.vbaexpress.com/forum/sho...book-into-one-with-a-dropdown-box-(hardcoded)
https://www.excelguru.ca/forums/sho...book-into-one-with-a-dropdown-box-(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?
 
Please amend my post to include the following links:
https://www.excelforum.com/excel-pr...a-worksheet-into-one-with-a-dropdown-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/sho...-a-dropdown-box-(hardcoded)&p=44952#post44952
https://www.ozgrid.com/forum/index....down-box-hardcode/&postID=1241076#post1241076
 
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:
[COLOR=#1A1A1B][FONT='inherit']C6:D7 for No Payments[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']=IF(ISBLANK(D6),"",(C6*D6))
C10, D10 & E10 for Credit on Account[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']=IF(ISBLANK(E10),"",(C10+E10))
C14, D14 & E14 for Debit on Account[/FONT][/COLOR]
[COLOR=#1A1A1B][FONT='inherit']=IF(ISBLANK(E14),"",(C14*D14-E14))[/FONT][/COLOR]


Is it reasonable to ask if the above verbiage is wrong or is it the attached file that's wrong ?
 
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.
 

Attachments

  • WCD EXPORT (1).xlsm
    54.7 KB · Views: 11
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?
 
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
 

Attachments

  • WCD EXPORT (1).xlsm
    41 KB · Views: 11
Last edited:
Amended:


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


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


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.
 
Using the reset buttons causes multiple columns to be hidden incorrectly instead of "Credit" in Cell F10.

I suggest you put STOP as the first instruction line in the Worksheet_Change macro,
when triggerred the code execution will stop at that point
hit the Windows Key and Right Arrow to put the VBA environment on the right hand side of your screen
then use the F8 key to step through the code one line at a time while being able to see what is happening to the spreadsheet.

I think you'll see things go goofy using select and selection (which are seldom needed) in the reset macro.
Try the macro this way and see if it makes a difference.
Code:
Sub Rst()
    Range("C10:F10").ClearContents
    Application.Columns("F").EntireColumn.Hidden = True
    Range("C10").Select
End Sub
 
Thank you very much NoS. I still hadn't cleaned up the code from making changes, I can see that now. Also, one of the wrong cells was unlocked.

Instalment & Debit reset are working well now. The Day & Discount reset isn't working at all though and the dropdown from C20 still moves down several cells when they are populated.

kxB2LM8.png
1qlBOdW.png

View attachment WCD EXPORT (1).xlsm
 
the dropdown from C20 still moves down several cells when they are populated.
re-select C20 before End If in the =("$C$20") section of the macro

The Day & Discount reset isn't working at all
executes the DADCReset macro for me
 
Thank you for your reply.

So it should look like this?
Code:
 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
        Range("C20").Select
        End If

That's not stopping the dropdown arrow and options moving down several cells.

The DADCReset macro isn't able to deal with hiding rows 21 to 34 after the discount dropdown box is cleared. It should behave the same way as the dropdown box does (hiding those rows). Selecting "no discount" hides those rows, this is what I need to replicate with the macro.

I've also noticed that the code I am using is preventing me from ticking or unticking the box in G16. It says the cell is locked. It should only be locked from being being edited by the user to prevent anything being typed, not everything.
 
Last edited:
So it should look like this?
No.
That's the End If of If Target.Value = whatever and its Else If statements
Not the End If of If Target.Address = ("$C$20")


The DADCReset macro isn't able to deal with hiding rows 21 to 34 after the discount dropdown box is cleared. It should behave the same way as the dropdown box does (hiding those rows). Selecting "no discount" hides those rows, this is what I need to replicate with the macro.
That's right, looking at the macro it's obvious there is nothing to hide the rows.
This is your opportunity to add it.


I've also noticed that the code I am using is preventing me from ticking or unticking the box in G16. It says the cell is locked. It should only be locked from being being edited by the user to prevent anything being typed, not everything.
This was not the case previously, what did you change ?
And can you tell me the purpose of the Worksheet_SelectionChange macro ?
 
Thank you, the level of guidance you are providing is really helping me to better understand.

So the code in its entirety should look like this?
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
            Range("C20").Select
        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:G16"
    Const sJUMP_TO_CELL As String = "C20"
    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

The reset macro needs to look like this:
Code:
Sub DADCReset()'
' DADCReset Macro
'


'
    Range("C20:G20,E16,D16,C16").Select
    Range("C16").Activate
    Selection.ClearContents
    Range("C16").Select
    Range("21:34").EntireRow.Hidden = True
    Range("C16").Select
End Sub

The problem was that the code was in debug mode and not executing properly.

The purpose of the Worksheet_SelectionChange is to enable to user to click the tickbox for inclusive/exclusive but prevent them selecting the cell to avoid them typing something in there that would break it. In the revised code, I unlocked G15 because that was causing the error and have instead protected that cell in the same way too.

I still can't get the bottom dropdown box content to stay put.
 

Attachments

  • WCD EXPORT (2).xlsm
    40.3 KB · Views: 11
The purpose of the Worksheet_SelectionChange is to enable to user to click the tickbox for inclusive/exclusive but prevent them selecting the cell to avoid them typing something in there that would break it. In the revised code, I unlocked G15 because that was causing the error and have instead protected that cell in the same way too.
:thumb:


Code:
Sub DADCReset()
'
' DADCReset Macro
'
    Range("C20:G20,E16,D16,C16").ClearContents
    Range("21:34").EntireRow.Hidden = True
    Range("C16").Select
    
End Sub


Code:
Option Explicit

Private Sub Worksheet_Activate()
'Call Rst
    'Range("C4").Select
    
    With Worksheets("Instalments")
        .Range("C10:F10").ClearContents
        .Columns("F").EntireColumn.Hidden = True
        .Range("C10").Select
    End With
    
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' MsgBox "The range that changed was" & vbLf & Target.Address(0, 0)    'for test purposes only
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Select Case Target.Address(0, 0)
        Case "G16"
            ActiveSheet.Shapes("CheckBox6").Visible = (Len(Target.Value) > 0)
        Case "C10"
            'Application.EnableEvents = False
            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
        Case "C20"
            If Target.Value = "No Discount" Then
                Range("21:34").EntireRow.Hidden = True
                Range("C20").Select
            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
            Range("C20").Select
    End Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const sCELL_TO_SKIP As String = "G15:G16"
    Const sJUMP_TO_CELL As String = "C20"
    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
 
Thank you very much, it's very close to where it needs to be now.

Two more things:
  1. Is there a way to make the fields that require a date flexible so that entering 010420 is the same as 01/04/2020?
  2. How do I make it so that only either: 25% Discount displays 21:24, 50% Discount displays 26:29 or 50% Discount & 25% Discount displays 31:34 without all rows displaying if the user changes the selection from the dropdown several times?

This shouldn't be possible:
zXQte6N.png

One selection should show one results over the corresponding rows and should never become an accumulation (contrary to what is pictured).
 

Attachments

  • WCD EXPORT (2).xlsm
    40.3 KB · Views: 2
Whilst I still don't know about the date formatting, I think I figured out 2.

Code:
        Case "C20"            If Target.Value = "No Discount" Then
                Range("21:34").EntireRow.Hidden = True
                Range("C20").Select
            ElseIf Target.Value = "25% Discount" Then
                Range("21:24").EntireRow.Hidden = False
                Range("25:34").EntireRow.Hidden = True
            ElseIf Target.Value = "50% Discount" Then
                Range("26:29").EntireRow.Hidden = False
                Range("21:25").EntireRow.Hidden = True
                Range("30:34").EntireRow.Hidden = True
            ElseIf Target.Value = "50% Discount & 25% Discount" Then
                Range("31:34").EntireRow.Hidden = False
                Range("21:30").EntireRow.Hidden = True
            End If
            Range("C20").Select
Is there a neater way of writing that?
 

Attachments

  • WCD EXPORT (2).xlsm
    41 KB · Views: 8
Back
Top