VBA Code to recognise that a drop down selection has been activated

[NeilMG]

Hi Team

I have developed a BP planning tool in Excel that shows, inter alia, 30 Year Cash Flows for Landlords who manage multiple properties. I have, on the face of that report, a drop down selection which enables the User to view the detailed Cash Flows either Including or Excluding the Cash Flows that relate to New Developments planned by the Landlord.

I want to be able to copy this Cash Flow Report to another Sheet initially Including New Development and then for the code to change that drop down to Excluding New Development and then copy this revised Cash Flow Report to the same sheet, but immediately below it; I can then calculate the changes in Cash Flows relating to New Development

I am not a coder myself, but do have a fairly good understanding when I view the code. I tried using the Macro Recorder initially, but this process failed to record the change affected by the drop down selection.

This is the Code generated:

Sub NewDevFunding()
'
' NewDevFunding Macro
'
Range("B3:AJ162").Select
Selection.Copy
Sheets("Wkg4 - General").Select
Range("B10944").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=154
Range("B11106").Select
Sheets("R3 Detailed Cash Flow").Select
Range("C6:C7").Select
Application.CutCopyMode = False
Range("B3:AJ162").Select
Selection.Copy
Sheets("Wkg4 - General").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A11103").Select
End Sub

My drop down cell is B7 on the Sheet "R3 Detailed Cash Flow"
as shown below

R3 - Detailed Cashflows - £000s - at Nominal Values

Select Cash Flow Basis and New Dev from drop downs…

Nominal_CF_Selected

Include_New_Dev

So when this macro runs, which will be button activated, what I need it to do is automatically check that for the 1st Copy the dropdown is selected as Include_New_Dev and, if not, change it before copying the report; it then needs to change the cell to Exclude_New_Dev then copy the report again.

I hope I have made my requirements clear and look forward to hearing back from one of you!

With Kind Regards

 

[CSmith]

Does this help any?

 

[NeilMG]

Does this help any?

Yes it did thanks. Managed to find a bit of code that used the Replace fuction which sorted it very neatly - here is the code as I tested it - which works fine!

    Dim OriginalText As String
    Dim CorrectedText As String
    Application.ScreenUpdating = False
    Application.Goto Reference:="SourceHome"
    OriginalText = Range("C4").Value
    CorrectedText = Replace(OriginalText, "Two", "One")
    Range("C4").Value = CorrectedText
    Range("C7:G7").Select
    Selection.Copy
    Application.Goto Reference:="ReportHome"
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Goto Reference:="SourceHome"
    CorrectedText = Replace(OriginalText, "One", "Two")
    Range("C4").Value = CorrectedText
    Range("C7:G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.Goto Reference:="ReportHome"
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D8").Select
    Application.Goto Reference:="SourceHome"
     CorrectedText = Replace(OriginalText, "Two", "One")
     Range("C4").Value = CorrectedText
    Application.Goto Reference:="ReportHome"
     Application.ScreenUpdating = True
End Sub

 

[CSmith]

Good to hear! Thank you for the update!

Yes it did thanks. Managed to find a bit of code that used the Replace fuction which sorted it very neatly - here is the code as I tested it - which works fine!