Userform Combo Box switches sheet but doesn't enter data into active sheet

[joburbank]

I have put together a Userform for entering data into a series of identical spreadsheets in the same workbook. The form contains a combo box that switches the active worksheet by selecting the sheet's number. However, once the new sheet is selected, the Userform continues to edit the cells in the original sheet (eg. Start with sheet 1; enter data; switch to sheet 2; data entered in the userform continues to enter values in sheet 1).

Is there a way to reset the user form without closing it so that the data will be entered into the active sheet?

Thanks

Code:

Private Sub SelectBidItem_Change()
'Code to select worksheet based on value in ComboBox

Sheets(SelectBidItem.value).Activate

End Sub


Private Sub userform_initialize()
' Code to populate values for list in ComboBox
With SelectBidItem
.AddItem "0"
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
End With
End Sub

 

[Jon von der Heyden]

Hi and welcome to the board.

There is nothing in the code attached that writes back to the sheet. Where is that bit of code?

 

[joburbank]

Thanks for the reply.

The userform uses a standard textbox with the control source set to a specific cell on the worksheet (d1). I didn't write additional code since I thought it was included as part of the text box. Do I need to write a sub-routine that tells the control to write to the active workbook? If so, how would that look?

Thanks

 

[Jon von der Heyden]

Would you be happy to commit the text to the sheet by clicking on a command button on the userform? You could use something like this attached to the command buttons click event:

ActiveSheet.Range("D1").Value = Me.TextBox1.Value

 

[joburbank]

Thanks.

I tried using the code you suggested ("ActiveSheet.Range("D1").Value = Me.TextBox1.Value") for the "TextBox_Change ()" event. It does allow me to enter values into other sheets, but it continues to enter the same values into the original worksheet simultaneously.

I am a bit perplexed. I can use the form on any sheet as long as I open it with the sheet I want to edit active. If I select another sheet from the form (making that active), it continues to enter data from the text box into the original worksheet. I may be able to create a seperate user form to select the sheet first and then open the form, but I'd like to avoid using multiple forms if I can.

Any suggestions??

Jerry

 

[Jon von der Heyden]

Hi Jerry

I suspect that is because you still have the control source set. I think you need to clear it.