combo Box Trouble

[Kavy]

Hello, I have a combo box, text box, Save button and a multi col list On a user form with vb excel

The combo box is filled with the index of the list ( 0 through Mylist.listcount-1)

The user enters data into the textbox, which after you click a save command, it appears in the list, on the row specifyed by the combo box.

Is there a way to save on the fly, like get rid of the button?
I tried to code it in the combo box but nothing comes up!

Here is what i was trying, I was trying to make it so what ever was in the spot on the list would appear and if the user edits it, it changes.
Thanks for any suggestions!

Private Sub ComRow_Change()
Dim k As Integer
dim i as integer

i = ComRow.listIndex
k = ComRow.ListCount
If ComRow.Text > i Or ComRow.Text < 0 Then
    MsgBox ("Please enter a value between 1 and " & k)
    Exit Sub
End If

If ComRow.Text = "" Then
    Exit Sub
End If

txtPM.Text = listEquip.list(i, 7)



listEquip.list(i, 7) = txtPM.Text

 

[mikerickson]

how about

saveButton.Visible = False

 

[Kavy]

Yes that will make my save button dissapper but it don't make my combo box auto save everytime i click it

 

[mikerickson]

What do you mean by "auto save"?
Your routine might have a problem if the listEquip variable isn't a Public variable.
What cell are you trying to populate with the textbox's value?

 

[Kavy]

The cells that get populated are on a worksheets, refereanced by rowc, and colc, not listed there.

I shouldn't of said Auto Save, I didn't mean saving the excel file, i ment when they change the combo box number, meaning when they change the index selected, the textbox text goes into a specific row into the list. this row is which ever number was shown in the combo box BEFORE they switched it

 

[mikerickson]

The _Change event is too volitile for my taste, so I put this routine in the _AfterUpdate event. It works in the Change event if you prefer that. It will put the contents of TextBox1 in the row number indicated by the previous selection from a comboBox of Sheet1, column A.

Private Sub ComboBox1_AfterUpdate()
Static lastValue As String

ThisWorkbook.Sheets("sheet1").Cells(lastValue, 1).Value = Me.TextBox1.Text
lastValue = Me.ComboBox1.Value

End Sub

 

[Kavy]

hey thanks! do i just creat a newsub with that name, with my combo box name instead of combobox1 ?

 

[Kavy]

I got it to work, except one probelm
Me.textbox1.text always returns the new text, not the previouse text.

Meaning
lastValue returns the value it was before I changed it, but the textwon't

 

[mikerickson]

OK..now I get it.
Put

Dim lastText as String

at the top of the Userform's code sheet, outside of any proceedure.
Then use the TextBop1_AfterUpdate routine to set lastText. Similar to this.

Option Explicit
lastText As Text

Private Sub TextBox1_AfterUpdate()
Static previousText as String
lastText = previousText
previousText = Me.TextBox1.Text
End Sub

Private Sub ComboBox1_AfterUpdate() 
Static lastValue As String 
ThisWorkbook.Sheets("sheet1").Cells(lastValue, 1).Value = lastText
lastValue = Me.ComboBox1.Value 
End Sub

 

[Kavy]

weak question, in laymans terms what does option explicict do, i no its like a super novice question!

And thanks as always!