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

joburbank

New Member
Joined
Apr 25, 2010
Messages
12
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 25, 2010
Messages
12
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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:

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

joburbank

New Member
Joined
Apr 25, 2010
Messages
12
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
 
Last edited:

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi Jerry

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

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,146
Members
414,365
Latest member
UUR

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