Userform and ComboBox Question

[Craig2010]

Hi All,

I've searched around a few threads and picked bits of code to end up with the code below on my Userform to transfer data. I'm sure it's very ugly but works at the moment. I need help with two things though. Firstly when the Userform opens I can't click on the worksheet behind it and say scrol down the sheet to see if it's all working properly without closing the Userform. The second is I would like a feature on the Combo box to Add a new name straight through the Combo Box that would then update the Source list as well. Is this possible? Any help appreciated....

Private Sub ADD_Click()
Dim LastRow As Object
Set LastRow = Sheets("Player Action").Range("A65536").End(xlUp)

With LastRow
.Offset(1, 0) = ComboBox1.Text
.Offset(1, 1) = DTPicker1.Value
.Offset(1, 2) = BUYIN.Value
.Offset(1, 3) = WINLOSS.Value

'clear the data
Me.ComboBox1.Value = ""
Me.BUYIN.Value = ""
Me.WINLOSS.Value = ""
Me.ComboBox1.SetFocus


Dim ws As Worksheet
Set ws = Worksheets("Player Action")

ws.Cells(5, 7).Value = Me.TextBox1.Value
ws.Cells(5, 8).Value = Me.TextBox2.Value
ws.Cells(5, 9).Value = Me.DTPicker1.Value

Label1.Caption = Sheet1.Range("g6").Value
Label2.Caption = Sheet1.Range("h6").Value
Label3.Caption = Sheet1.Range("g7").Value
Label4.Caption = Sheet1.Range("h7").Value
End With

End Sub
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[matsgroe]

Hi

To be able to scroll while userform is shown, you must change the properties to the userform, with or without macro.

if you just click on the userform in VBA mode, you get all its properties and change the value on "show modal" to false.

hmm, i don't know where you get the combobox values from. but u can use "with"

with userform#.combobox1
.rowsource = ""
.additem "item2"
.additem "item3"
end with
userform#.show

hopefully that helps ?

 

[Craig2010]

Hi matsgroe,

Thanks for the first tip, that worked and I learnt something new.

The ComboBox is on the Userform and it gets it's data from a Sheet called Players. I have this in it's Row Source in the Properties??

=OFFSET(Players!$A$3,0,0,COUNTA(Players!$A:$A),1)

Is that how I should have it? From what I read about that Formula it will expand to include any new names added to the list.

Where would I place that code you suggested to try?

Thanks...
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[matsgroe]

Thats good

you need to put it in the sub that show the userform

and you need to change to the right userform name and combo box

With UserForm#.combobox1
.rowsource = "=OFFSET(Players!$A$3,0,0,COUNTA(Players!$A:$A),1)"
.AddItem "item2"
.AddItem "item3"
End With
UserForm#.Show

 

[Craig2010]

Hi,

I placed the code there but it didn't seem to make any difference. What I was hoping was that if you tried to enter something not in the ComboBox it would stop you but give you some sort of pop up that would let you add to the list. It's ok as it is as because you can enter a name not in the list. When entering all the new data you can always just copy and paste the new names entered from the data worksheet and add them to the ComboBox range for the future. I appreciate your help...Cheers
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">