VBA Question

[NotVeryGoodAtBI]

Hello, I have posted my excel VBA code below, I made it so when I double clicked on cells AP6:AP85 the user would be prompted to choose "Fixed" or "Variable" to use in subroutine get_supplier_rate. But when a user picks Fixed or Variable it replaces the original numeric value that was in cells AP6:AP85. I've tried a stored procedure but couldn't get it to work. Does anyone know what I can do so the user input choice of Fixed or Variable will only be used in the get_supplier_rate function and not replace the numeric values in cells AP6:AP85?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim cellRow As Integer
    If Not Intersect(Target, Me.Range("X6:X85")) Is Nothing Then
        cellRow = Target.Row
        Call maturity_simulated_data(Me.Cells(cellRow, 1), Me.Cells(cellRow, 24))
    ElseIf Not Intersect(Target, Me.Range("AP6:AP85")) Is Nothing Then
        cellRow = Target.Row
       
        ' Apply data validation to the cell
        With Me.Range("AP" & cellRow).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Fixed,Variable"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
       
        ' Prompt message for the user to make a selection
        MsgBox "Please select rate type from the drop-down menu.", vbInformation
       
        ' Cancel the double click event to avoid unexpected behavior
        Cancel = True
       
    End If
End Sub

' Worksheet change event to handle the selection made from the drop-down menu
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellRow As Integer
   
    If Not Intersect(Target, Me.Range("AP6:AP85")) Is Nothing Then
        cellRow = Target.Row
        If Target.Value = "Fixed" Then
            Call get_supplier_rate(Me.Cells(cellRow, 1), "Fixed")
            Sheets("Supplier rate").Visible = True
            Sheets("Supplier rate").Select
        ElseIf Target.Value = "Variable" Then
            Call get_supplier_rate(Me.Cells(cellRow, 1), "Variable")
            Sheets("Supplier rate").Visible = True
            Sheets("Supplier rate").Select
        End If
    End If
End Sub

 

[RoryA]

Why don't you just pop up an inputbox for them to enter whichever one they want (you could prompt with 1 for Fixed and 2 for Variable to make it easier) rather than changing the cells at all?

 

[NotVeryGoodAtBI]

Why don't you just pop up an inputbox for them to enter whichever one they want (you could prompt with 1 for Fixed and 2 for Variable to make it easier) rather than changing the cells at all?

Unfortunately that's just what I was tasked with, I originally had an input box where user will input F or V.

 

[RoryA]

I don't think whoever told you to do it this way really thought it through.

You could do something like this:

' Worksheet change event to handle the selection made from the drop-down menu
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellRow As Integer
   
    If Not Intersect(Target, Me.Range("AP6:AP85")) Is Nothing Then
        cellRow = Target.Row
        Dim currentValue
        currentValue = Target.Value
        with Application
             .enableevents = False
             .Undo
             .Enableevents = true
        End with
        If currentValue = "Fixed" Or currentValue = "Variable" Then
            Call get_supplier_rate(Me.Cells(cellRow, 1), currentValue)
            Sheets("Supplier rate").Visible = True
            Sheets("Supplier rate").Select
        End If
    End If
End Sub