Help with UserForm

walber · #1 01-16-2017, 07:07 PM

good evening everyone

I searched on websites and did not find my answer.
How do I get this code to work when I click G16 line from the Menu spreadsheet according to the template attachment.

Code:

Option Explicit
Private TextoDigitado As String
Private Sub ListBox1_Click()
ActiveCell.Value = ListBox1.Value
Unload Me
End Sub

Private Sub TextBox1_Change()
TextoDigitado = TextBox1.Text
Call PreencheLista
End Sub

Private Sub UserForm_Initialize()
Call PreencheLista
End Sub

Private Sub PreencheLista()
Dim ws As Worksheet
Dim i As Integer
Dim TextoCelula As String
Set ws = ThisWorkbook.Worksheets(2)
i = 2
ListBox1.Clear
With ws
While .Cells(i, 2).Value <> Empty
TextoCelula = .Cells(i, 2).Value
If UCase(Left(TextoCelula, Len(TextoDigitado))) = UCase(TextoDigitado) Then
ListBox1.AddItem .Cells(i, 2)
End If
i = i + 2
Wend
End With
End Sub

Thanks in advance

NoSparks · #2 01-17-2017, 11:22 AM

If you're asking how to show the user form when you click, tab or enter into C16, which is merged up to M16,
try this in the Menu sheet module

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$G$16:$M$16" Then UserForm1.Show
End Sub

If that's not what you're asking... I have no idea

walber · #3 01-18-2017, 12:49 PM

And exactly what I wanted.
thank you

How to make Excel go to a certain cell when I hit Enter
example
A1 to A3 to A5 to A7 to A9 to A11

Kev · #4 01-30-2017, 12:12 AM

Try this - put in a sheet module
(I assume you are editing the cell and then wanting to move 2 rows down after edit)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange as range
    Set myrange = Union(Range("A3"), Range("A5"), Range("A7"))
    If Intersect(myrange, Target) Is Nothing Then Exit Sub
    Target.Offset(2, 0).Select
End Sub

Make your range specific to what you want

To apply to column A only:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange as range
    Set myrange = Columns("A:A") 
    If Intersect(myrange, Target) Is Nothing Then Exit Sub
    Target.Offset(2, 0).Select
End Sub

To apply to every cell in the worksheet:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Offset(2, 0).Select
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 PM
Help with VBA userform	benn	PowerPoint	1	03-02-2015 11:32 AM
Keep Userform on Top	didjee	PowerPoint	5	10-20-2014 02:22 PM
VBA Code in a UserForm module to delete a Command Button which opens the userform	Simoninparis	Word VBA	2	09-21-2014 03:50 AM
Is it possible to take an input from a UserForm in one document to a UserForm in a do	BoringDavid	Word VBA	5	05-09-2014 09:08 AM