UserForm help!

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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}
Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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

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

And this is the standard module code
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Why use userform?
What are you trying to do?
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Macro1
End Sub
in Standard Module
Code:
Sub Macro1()
    UserForm1.Show
End Sub
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top