• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Re-activate active cell when userform is shown

W

willheap

Member
I have a range of cells that require the user to input data.
On selecting cell to input data, a userform shows (similar to a comments box) with all the information the user would need to populate the correct data in the cell.

However, once the userform shows, the focus is set to the userform and leaves the worksheet. The userform is set up as modeless so it stays open while selecting cells on the worksheet but I cannot work out how to re-activate the ActiveCell so that the user doesn't have to use the mouse to click on the activecell again to enter data (or scroll on the worksheet).

I've tried and failed with;
Activesheet.activate
Activesheet.select
Activecell.activate
Activecell.select

Any ideas?
 
Good idea..
The file UF Test 1 shows a very basic version of what I use the userform for.
Selecting a cell to input data shows the userform. But, the cell is no longer active until you mouse-click back on the worksheet.
 

Attachments

  • UF Test 1.xlsm
    21 KB · Views: 34
I've tried to reactivate the activecell at the end of the userform initialise code and also after calling the userform from the worksheet selection change event. I don't know where else I could try the code that would be active at this time.
 

In fact nothing to do ! But just select a cell by a double clic : cell doesn't lose focus while UserForm appears !​
 
That would work but the problem is, a lot of users of this template use the cursor keys, or simply press enter to navigate the sheet so once the userform appears they can't edit the selected cell. I can use the keydown event to continue navigating the sheet by cursor key but i can't edit cells when the userform appears.
I need some code that does the job of the second click on the cell... I just can't work it out.
 

In pure VBA inner statement : no way !

Maybe via a Windows system API to simulate a click back to the worksheet or to activate it, try on a VB forum …
​
 
Dear willheap,

This is what you are looking for!!!

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'/// clear any open userforms
For Each uf In UserForms
Unload uf
Next uf
'/// show uf with descriptions from adjacent cells
If Not (Target.Column = 2 And Target.Row > 2) Or _
Not Target.Cells.Count = 1 And _
Not Target.Offset(0, 1) = vbNullString Then Exit Sub
Txt = _
Target.Offset(0, 1).Value & Chr(10) & Chr(10) & _
Target.Offset(0, 2).Value & Chr(10) & Chr(10) & _
Target.Offset(0, 3).Value & Chr(10) & Chr(10) & _
Target.Offset(0, 4).Value & Chr(10) & Chr(10) & _
Target.Offset(0, 5).Value
UserForm1.Label1.Caption = Txt
UserForm1.Show False
AppActivate Application.Caption
End Sub
 
The this example file for the topic can be reviewed :Listbox displaying when cell in specific area is selected.

It's codes :

Code: 
PrivateSub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i AsLong
  IfNot Intersect(Range("A:A"), Target)IsNothingAnd Target.Count =1And Target.Address(False,False)<>"A1"Then
  Me.ListBox1.MultiSelect = fmMultiSelectMulti
  Me.ListBox1.List = Sheets("List").Range("A2:A"& Sheets("List").Cells(Rows.Count,1).End(xlUp).Row).Value
  For i =0ToMe.ListBox1.ListCount -1
  If Target.Value <> Empty AndMe.ListBox1.List(i,0)= Target.Value Then
  Me.ListBox1.Selected(i)=True
  EndIf
  Next i
  Me.ListBox1.Top = Target.Top
  Me.ListBox1.Left = Target.Left + Target.Width
  Me.ListBox1.Visible =True
  Else
   Me.ListBox1.Visible =False
  EndIf
  i = Empty
  EndSub
...

Source for example file
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top