VBA input box on clicking cell

[apkdsmith]

I'm sure this question must have been asked somewhere, but I haven't been able to find it.

Is it possible to bring up an input box in VBA by clicking on a cell or its contents? Like a hyperlink sort of thing? Ideally, I don't want to have to introduce a button, as I would like to be able to do this for each cell in a list, so that the contents of the input box varies depending on the cell clicked on.

 

[Pascal]

Hi,

Here you go: -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address = "$B$1" Then
        ShowForm
    End If
    
End Sub

Sub ShowForm()

    UserForm1.Show
    
End Sub

Regards

 

[apkdsmith]

Thanks. Can you explain how this works, please? I think I need to be able to understand it to implement it correctly? Also, does it just go in the relevant sheet?

 

[Pascal]

Hi,

The above is for a sheet, I had it against Sheet1.

It then monitors if the Selection on the Active Sheet (Sheet1) changes, if it does it checks the 'Target' of the change, in this case it checks to see if it's B1. If this is the case it then calls the other Sub Routine which in turn shows the form.

Yes, that code is for the relevant sheet.

Regards

 

[apkdsmith]

Thanks very much for your help