Solved: combobox populate

[fgarcia90]

how can i populate a combobox with cells A1, C1, E1, G1 and when choose go to the first row of that collum that is empty like cell focus???? so I write something...

thanks in advanced

[Zack Barresse]

Hello! Not sure what kind of controls you're talking about. I'm assuming you're meaning worksheet controls and not a modal userform, although you haven't specified. Assuming it's worksheet controls, you would need a worksheet change event. It would go into your worksheet code module. Easiest way to get there is to right click the worksheet tab name and select View Code. Paste this in there (assuming you don't' already have this event in there, otherwise we'd need to incorporate it, as you can only have one event type per worksheet).

This code assumes you've used an ActiveX control, and that it's labeled "ComboBox1":
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Row = 1 Then
Me.ComboBox1.Clear
Else
If Me.ComboBox1.ListCount = 0 Then
Me.ComboBox1.AddItem Me.Range("A1").Value
Me.ComboBox1.AddItem Me.Range("C1").Value
Me.ComboBox1.AddItem Me.Range("E1").Value
Me.ComboBox1.AddItem Me.Range("G1").Value
End If
End If
End If
End Sub

HTH

[fgarcia90]

sorry but i'm very,very new in this...
the combobox is in a form that appears after user confirmation
the cells are fixed with strings and i copy like this and nothing appears in my combox1 :(





Private Sub form_user_inicial_tecnica_Initialize()
If
Target.Cells.Count = 1 Then
If
Target.Row = 1
Then

form_user_inicial_tecnica.ComboBox1.Clear

Else
If
form_user_inicial_tecnica.ComboBox1.ListCount = 0
Then

form_user_inicial_tecnica.ComboBox1.AddItem
Sheet2.Range("A1").Value

form_user_inicial_tecnica.ComboBox1.AddItem
Sheet2.Range("C1").Value

form_user_inicial_tecnica.ComboBox1.AddItem
Sheet2.Range("E1").Value

form_user_inicial_tecnica.ComboBox1.AddItem
Sheet2.Range("G1").Value

End If
End
If
End If
End Sub

[Zack Barresse]

If this is a userform, then the code I supplied would not work. We just need to know what it is you're working with. The more details you provide, the faster and more succinctly you'll receive solutions. :)

If you want these values to load in a combobox on a userform, depending on what cells is selected at the time (row 1 or not), and assuming your combobox control is named "", you could use this as your initialize routine...

Private Sub UserForm_Initialize()
Dim WS As Worksheet
Dim rCell As Range
Set WS = ActiveSheet
Set rCell = ActiveCell
If rCell.Row = 1 Then
Me.ComboBox1.Clear
Else
If Me.ComboBox1.ListCount = 0 Then
Me.ComboBox1.AddItem WS.Range("A1").Value
Me.ComboBox1.AddItem WS.Range("C1").Value
Me.ComboBox1.AddItem WS.Range("E1").Value
Me.ComboBox1.AddItem WS.Range("G1").Value
End If
End If
End Sub

HTH

[fgarcia90]

hi again,

it did not work so i puted direct in the Rowsource properties and is working, but stiil don't have the slytest ideia how to send the focus to a first empty cell of a collum depending on the answer so i started like this


Private Sub ComboBox2_Change()
If ComboBox2.Value = Técnica Then
code collum A
ElseIf ComboBox2.Value = Comercial Then
code collum C
ElseIf ComboBox2.Value = Armazém Then
code collum E
Else
code collum G
End Sub


but what do i put to replace "code"

[Zack Barresse]

What type of control is it, and where is it at? Is it on the worksheet? Is it in a userform? Please, give us more information or post a sample of your file. We can't help you if we don't know what you're working with. :)

[fgarcia90]

let me try to simplify at this stage :)

what code do I need to search the first empty row in sheet1, collum A...
and put the that cell with focus(?) ou activate (?)

sorry very, very new at this lol

[Zack Barresse]

No worries. :) Please try to explain what you're trying to accomplish. Be as detailed as you possibly can. The more details we understand about what you're trying to accomplish, we can more easily provide a solution. For example, I still don't know what types of controls you're working with. If you can't explain it, can you upload a sample file?

[Tinbendr]

Private Sub ComboBox2_Change()
If ComboBox2.Value = Técnica Then

iRow = Application.WorksheetFunction.MATCH("*",A:A,-1)

ElseIf ComboBox2.Value = Comercial Then

iRow = Application.WorksheetFunction.MATCH("*",C:C,-1)

ElseIf ComboBox2.Value = Armazém Then

iRow = Application.WorksheetFunction.MATCH("*",E:E,-1)

Else

iRow = Application.WorksheetFunction.MATCH("*",G:G,-1)

End Sub

[fgarcia90]

the rows starting by irow appears in red??? why maybe I need to declare someting...

[Tinbendr]

Sorry, that should be...
iRow = Application.WorksheetFunction.Match("*", Sheet1.Range("A:A"), -1)

[fgarcia90]

sorry but it does do anything...






Private Sub ComboBox2_Change()


If ComboBox2.Value = Técnica Then

form_user_inicial_tecnica.Hide
Application.Visible = True
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("A:A"), -1)


ElseIf ComboBox2.Value = Comercial Then

form_user_inicial_tecnica.Hide
Application.Visible = True
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("C:C"), -1)


ElseIf ComboBox2.Value = Produção Then

form_user_inicial_tecnica.Hide

Application.Visible = True
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("E:E"), -1)


ElseIf ComboBox2.Value = Contabilidade Then

form_user_inicial_tecnica.Hide

Application.Visible = True
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("G:G"), -1)
End If
End Sub

[Tinbendr]

sorry but it does do anything...

If ComboBox2.Value = Técnica Then
Is Técnica a variable or a literal?

Should it be;
If ComboBox2.Value = "Técnica" Then

[fgarcia90]

starting to get somewhere dah of me of course this "" was lacking


now it goes to the sheet but it does not goes to the first empty row of the collum indicated.... any suggestion




Private Sub ComboBox2_Change()


If ComboBox2.Value = "Tecnica" Then
form_user_inicial_tecnica.Hide
Application.Visible = True
Sheet2.Visible = xlSheetVisible
Sheet1.Visible = xlSheetVeryHidden
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("A:A"), -1)


ElseIf ComboBox2.Value = "Comercial" Then
form_user_inicial_tecnica.Hide
Application.Visible =True
Sheet2.Visible =xlSheetVisible
Sheet1.Visible = xlSheetVeryHidden
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("C:C"), -1)


ElseIf ComboBox2.Value = "Produção" Then
form_user_inicial_tecnica.Hide
Application.Visible = True
Sheet2.Visible = xlSheetVisible
Sheet1.Visible = xlSheetVeryHidden
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("E:E"), -1)


ElseIf ComboBox2.Value = "Contabilidade" Then
form_user_inicial_tecnica.Hide
Application.Visible = True
Sheet2.Visible = xlSheetVisible
Sheet1.Visible = xlSheetVeryHidden
irow = Application.WorksheetFunction.Match("*", Sheet2.Range("G:G"), -1)
End If
End Sub

[Tinbendr]

OK make sure the shortcut name is correct.

Or use Worksheets("Sheet Name").Range("A:A"), -1) +1

We need to add one to the end. The formula finds the last cell with data, but we need the next empty one.

[fgarcia90]

yes the sheet name is ok, I think (in my new adventure in VBA programs) that I need to declare irow as something :) and there is another thing I'm not sure... ".Match" and after the "*" is for what getting the last row with data?
just to learn :)

[fgarcia90]

Simple question... (I think)


Option Explicit

Sub combos()
form_principal.cod_cat_prod.List = Sheet5.Range("C2:C30").Value
End Sub

If I want to search the last row with content of that collum and just show
those...

Anyone...