Excel VBA UserForm to Display Cell Location

m1ngle · Sep 28th 2016

Hello!

I am constructing a macro which detects validation errors. When it gets to the error, it displays a simple userform. I would LIKE the userform to display the location of the cell. So, I put in a text box. I'm not in love with the textbox, because I don't want the user to edit the data, I just want it displayed. My userform has two buttons, 'Next' and 'Close'. Here's my code.

Code

Private Sub UserForm_Initialize()
Dim cTextBox As Control
Dim r As Long, c As Range
Dim Loc As Characters
Set cTextBox = Controls.Add("Forms.TextBox.1")
With cTextBox
.Height = 20
.Width = 200
.Left = 15
cTextBox = CStr(ThisWorkbook.Sheets("DATA INPUT SHEET").Range(ActiveCell.Address).Address(False, False))
End Sub

Display More

Also, since this is an 'Initialize' function, it only pulls the value of the first cell. So if the error is in say Q19, the value remains Q19 no matter how many times I press 'Next'.

Does anyone have any ideas?

Thanks!

mrmmickle1 · Sep 29th 2016

Re: Excel VBA UserForm to Display Cell Location

Just lock the textbox so it's not editable:

Code

Private Sub UserForm_Initialize()
    Dim cTextBox As Control
    Dim r As Long, c As Range
    Dim Loc As Characters
    Set cTextBox = Controls.Add("Forms.TextBox.1")
    With cTextBox
        .Height = 20
        .Width = 200
        .Left = 15
        cTextBox = CStr(ThisWorkbook.Sheets("DATA INPUT SHEET").Range(ActiveCell.Address).Address(False, False))
        .Locked = True
    End With
End Sub

Display More

m1ngle · Sep 29th 2016

Re: Excel VBA UserForm to Display Cell Location

So great! Thank you very much.

mrmmickle1 · Sep 29th 2016

Re: Excel VBA UserForm to Display Cell Location

You're welcome. Glad you got it working.

Excel VBA UserForm to Display Cell Location

Participate now!

Share