UserForm help!

[omairhe]

Hey guyz, I need to ask you for some help. I downloaded a UserForm named "AlphabetForm" and inside it is a textbox named "TextBox1". When I will type inside that textbox it will automatically take me to the target.row by matching the values in Column A with that of TextBox1. However, I need this interface to be somewhat seemless to the end-user.

So,

1. Is it possible to have the UserForm loaded but invisible to the naked eye and

2. Would it be possible if I type in any of the small letters eg. a-z, no matter which excel's Cell I am on, it should get typed in the TextBox1 of the hidden UserForm. But should type in Cell instead only if Numerical or Capital letters A-Z are typed in.

Thanks for all your help.

 

[Yongle]

What are you trying to achieve?

To convert all input to upper case, paste code below into sheet module
right-click sheet tab \ select View Code \ paste code in window which appears \ go back to Excel with {ALT}{F11}

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Or Target.HasFormula Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

 

[omairhe]

Hey Yongle, Sorry for bad English. On further researching I made it this far. I will post this code that goes in the worksheet's code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "{096}", "Macro1"
End Sub

And this is the standard module code

Sub Macro1()
UserForm1.Show
End Sub

as a result whenever I press 0 on my numeric keypad, the UserForm is showed but 0 is not being typed in.

I want 0 to be typed in textbox1

 

[Yongle]

Why use userform?
What are you trying to do?

 

[omairhe]

Why use userform?
What are you trying to do?

Ok. I want to get to cell that begins with a or b or c....

Suppose that my List of several words are in Column B aragned alphabetically.

Column B
apple
banana
energy
green
light
zebra

Now I press keyboard letter g . This should take me to green.

So you see its a row selection based on the letter pressed.

Thanks

 

[omairhe]

I'd be most happy if I could omit using userform for such a little function.
I'm sure a Macro will serve as much better option. If I knew how to make one. lolz

 

[Yongle]

Can amend how this works later, after I understand what you want

After adding the code, select a different cell and type a letter into textbox
VBA below identifies first matching cell where first character = letter entered in Textbox and returns cell reference in message box
What should happen after you find that cell?

in Userform module

Private Sub TextBox1_Change()
    Dim rng As Range, cel As Range, t As String
    t = UCase(TextBox1.Text)
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    For Each cel In rng
        If UCase(Left(cel, 1)) = t Then
            MsgBox cel.Address(0, 0)
            End
        End If
    Next cel
End Sub

in Sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Macro1
End Sub

in Standard Module

Sub Macro1()
    UserForm1.Show
End Sub