SendKeys "{DOWN}"

J

Jaye7

Well-known Member

Joined

Jul 7, 2010

Messages

1,069

• Mar 27, 2011

• #2

Please is there anyone that can help.

 

Upvote 0

xenou

MrExcel MVP

Joined

Mar 2, 2007

Messages

16,836

Office Version

1. 2019

Platform

1. Windows

• Mar 27, 2011

• #3

This seems to work. It appears the key input is abandoned in some cases so we need to process the message queue.

Code:

Sub Bar()
    SendKeys "{Down}"
    DoEvents
    MsgBox ActiveCell.Address
End Sub

This appears to work equivalently, without sendkeys - I would generally avoid sendkeys unless there is other option:

Code:

Sub Foo()
    ActiveCell.End(xlDown).Select
    If Not Len(ActiveCell.Value) = 0 Then
        MsgBox ActiveCell.Address
    Else
        '//End of filtered list has been reached
    End If
End Sub

 

Upvote 0

J

Jaye7

Well-known Member

Joined

Jul 7, 2010

Messages

1,069

• Mar 27, 2011

• #4

Xenou,

The first script works great and I agree, I would rather not use send keys, I tried your second example and it isn't going to the next visible cell it is going to the last visible cell, can you please help with this.

 

Upvote 0

xenou

MrExcel MVP

Joined

Mar 2, 2007

Messages

16,836

Office Version

1. 2019

Platform

1. Windows

• Mar 27, 2011

• #5

Oh, right. Well, I have used a loop for this in some cases. Should work, especially if dataset is not many (tens of) thousands of rows would still be fast. Otherwise, not sure. Depends on what needs to be done. Guess there's always SendKeys {grin}.

Code:

Sub Foo()
    
    Dim r As Range
    Set r = ActiveCell
    Do
        Set r = r.Offset(1)
    Loop While r.EntireRow.Hidden = True
    
    r.Select
    If Len(r.Value) > 0 Then
        MsgBox r.Address
    Else
        '//End of List
    End If
    
End Sub

 

Upvote 0

J

Jaye7

Well-known Member

Joined

Jul 7, 2010

Messages

1,069

• Mar 27, 2011

• #6

That's perfect, thank you very much.

Maybe you could help with the following also as it is really annoying me.

http://www.mrexcel.com/forum/showthread.php?t=539186

 

Upvote 0

xenou

MrExcel MVP

Joined

Mar 2, 2007

Messages

16,836

Office Version

1. 2019

Platform

1. Windows

• Mar 27, 2011

• #7

I'm not enough of an expert to tweak the appearance of disabled controls but I do see a "Locked" property on a userform textbox. A locked textbox has a normal appearance. Set Tab Stop to false. Users can still mouse into it, though (but not do anything else), so it's not a perfect solution.

You could also use a label for the purpose of showing text, though it probably doesn't look exactly like a normal textbox either.,

 

Upvote 0

J

Jaye7

Well-known Member

Joined

Jul 7, 2010

Messages

1,069

• Mar 27, 2011

• #8

Thanks anyway Xenou, I don't want people to access the box at all until they hit the modify key on my form which then allows them to edit the textboxes, unfortunately to do this it seems that you have to use the enable=false script which fades out the font.

 

Upvote 0