SendKeys "{DOWN}"

[Jaye7]

Hi Everyone,

I was provided the following script to use arrow down rather than activecell.offset as I have a filtered list and want to select the next cell down that is visible.

SendKeys "{DOWN}"

My problem now is that if I try to access the cell using the script the address is the old cell address before the arrow down script has run.

I.e. I run the arrow down script and then textbox4.value = activecell.address, yet it is not the activecell it is the previous cell which I don't want it to be.

 

[Jaye7]

Please is there anyone that can help.

 

[xenou]

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

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:

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

 

[Jaye7]

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.

 

[xenou]

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}.

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

 

[Jaye7]

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

 

[xenou]

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.,

 

[Jaye7]

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.