non-modal userform to reflect values with Worksheet_SelectionChange

[Ctrl]

Hi All :hi:

Is it possible to use a non-modal userform which can reflects the values of columns B, BR, BS and BT depending on the current row starting from
row 6 to 60?

And this userform should not reflect values of these columns on other rows.

So let's assume that the current selected row is 10 then this userform would be like this:

Data1: (B10) Data2: (BR10) Data3: (BS10) Data4: (BT10)

Data will be used as a caption.

Finally, how can i use this userform with Worksheet_SelectionChange so it can update the values each time i move from one row to another?

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
With UserForm1
.Data1.Caption = Me.Range("B" & Target.Row).Value
.Data2.Caption = Me.Range("BR" & Target.Row).Value
.Data3.Caption = Me.Range("BS" & Target.Row).Value
.Data4.Caption = Me.Range("BT" & Target.Row).Value
End With
End Sub

[Ctrl]

Thank you xld

I put your code in sheet1 code window and added this code to show userform1 as modeless


UserForm1.Show vbModeless

now it is like this


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
UserForm1.Show vbModeless
With UserForm1
.Data1.Caption = Me.Range("B" & Target.Row).Value
.Data2.Caption = Me.Range("BR" & Target.Row).Value
.Data3.Caption = Me.Range("BS" & Target.Row).Value
.Data4.Caption = Me.Range("BT" & Target.Row).Value
End With
End Sub

It does reflect the values I want but the problem is that this userform is blocking the way on working on excel freely.
I want it to update the information then back to excel.

Is there a way to de-active the userform while being visible?

[Bob Phillips]

What's the point of putting the data on the form if you have it hidden.

[Ctrl]

Actually, I don't want it to be hidden. But maybe userforms can't work like this.


As you noticed, from Column B to Columns BR, BS and BT is a long way to go.
So this userform if it works will make it possible to avoid the need to scroll back and forth to see the results.

If I can reflect the values on the statusbar it would be enough for me.

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayStatusBar = True
Application.StatusBar = _
Me.Range("B" & Target.Row).Address(False, False) & ": " & Me.Range("B" & Target.Row).Value & ", " & _
Me.Range("BR" & Target.Row).Address(False, False) & ": " & Me.Range("BR" & Target.Row).Value & ", " & _
Me.Range("BS" & Target.Row).Address(False, False) & ": " & Me.Range("BS" & Target.Row).Value & ", " & _
Me.Range("BT" & Target.Row).Address(False, False) & ": " & Me.Range("BT" & Target.Row).Value
End Sub

[Ctrl]

This is really neat :thumb

Thank you xld for this brilliant solution.

[Bob Phillips]

It was your idea :smile: