Enter value into cell with an InputBox when a radio button is selected.

[angusfire]

I have a worksheet named "Main" that I would like to have an InputBox to pop up when a radio button is selected; (Option Button 3 with value = 1 in cell A311), and have the value of the InputBox placed into cell R2. I have placed the following code directly into the objects sheet but can get it to only run when I assign the macro directly to the radio button. I would like to set it up as a private sub and run based just on the selection of the radio button without having to assign the button a macro. Please help!

Sub Pumped_WS()

Dim userinput As String
        
    Set pumped = ActiveSheet.Range("A311")
    Set ws = ActiveSheet.Range("R2")
    
    If pumped.Value = 1 Then userinput = Application.InputBox("Please enter elevation for the pumping water surface!")
        
        ws.Value = userinput

End Sub

 

[dmt32]

Hi,
I don’t think the Forms controls OptionButtons assigned to a range will trigger any of the worksheets events but you can get around this by using a helper cell.

See if following does what you want:

Place following code in you worksheets CODE PAGE:

Private Sub Worksheet_Activate()    
   Me.Cells(1, Me.Columns.Count).Formula = "=$A$311"
End Sub


Private Sub Worksheet_Calculate()


    If Me.Cells(1, Me.Columns.Count).Value = 1 Then Pumped_WS Target:=Me.Range("R2")


End Sub

Place following in a STANDARD module:

 Sub Pumped_WS(ByVal Target As Range)

    Dim userinput As Variant
    
    userinput = Application.InputBox("Please enter elevation for the pumping water surface!", "Enter Elevation", , , , , 2)
    If VarType(userinput) = vbBoolean Or Len(userinput) = 0 Then Exit Sub
    
    Target.Value = CStr(userinput)


End Sub

You will need to activate the sheet (select it) to apply a helper formula in last cell row 1.
This is used to trigger Calculation event which should call your procedure if value of A311 = 1.

Hope Helpful

Dave