combo Box Trouble

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
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!

Code:
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
Yes that will make my save button dissapper but it don't make my combo box auto save everytime i click it
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,316
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

Well-known Member
Joined
Jun 25, 2007
Messages
607
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,316
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.

Code:
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

Well-known Member
Joined
Jun 25, 2007
Messages
607
hey thanks! do i just creat a newsub with that name, with my combo box name instead of combobox1 ?
 

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,316
OK..now I get it.
Put
Code:
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.
Code:
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

Well-known Member
Joined
Jun 25, 2007
Messages
607
weak question, in laymans terms what does option explicict do, i no its like a super novice question!

And thanks as always!
 

Forum statistics

Threads
1,181,102
Messages
5,928,073
Members
436,587
Latest member
Slicesofquince

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