I am sorry to be posting on this subject again but I have spent hours
mucking around in help files and google searches with no success. The end
result is confirmation that I don't know what the hell I'm doing in VBA so
I'll describe what I'm trying to do and gratefully accept any advice.

I have inserted a user form (userform1)as a VBA object with the following
objects: combobox1, textbox1, CommandButton1 and CommandButton2.
This is what I want the code to do:
-combobox1-range=sheet!3 A3:A200 and destination cell=C3.
-textbox1- destination cell = R3.
-CommandButton1- Enter data and close the userform.
- CommandButton2-Cancel.

Regards
gregork

Hi gregork,

1. To fill the combo box with a range, put the following code to
Private Sub UserForm_Initialize() event of the form.Should be look like
this :

Dim i as integer
For i = 3 to 200
Me.ComboBox1.AddItem Cells(i,1) 'i = Rownr 1 is the columnnr
next

2. To insert the data into the sheet, put this code to Private
Command1_OnClick() event of the Command1 Button :
If me.ComboBox1.Value<"" And Not IsNull(me.ComboBox1) And
Me.TextBox1.Value<"" And Not Isnull(Me.TextBox1) then
Cells(3,3) = Me.ComboBox.Value
Cells(3,18) = Me.TextBox1.Value
Else
Msgbox "Please complete form"
End if


---
Message posted from http://www.ExcelForum.com/

Hi Tolgag,


Thanks for the reply. I'm sorry to say I can't get the code to work.
1. With the combo box code I get compile error messages when I try to put
numbers in place of the i.What exactly am I supposed to input where you have
the i.... e.g Dim i as integer 'and' For i = 3 to 200.Is there a sheet
reference I am supposed to put in there somewhere?

2. I can't get a " Command1_OnClick()" event from the drop down lists at the
top of the code page. The nearest I can get is " Private Sub
CommandButton1_Click()".

I must apologise for my VBA dyslexia and thank you for your help.

regards
gregork


"tolgag " wrote in message
...
Hi gregork,

1. To fill the combo box with a range, put the following code to
Private Sub UserForm_Initialize() event of the form.Should be look like
this :

Dim i as integer
For i = 3 to 200
Me.ComboBox1.AddItem Cells(i,1) 'i = Rownr 1 is the columnnr
next

2. To insert the data into the sheet, put this code to Private
Command1_OnClick() event of the Command1 Button :
If me.ComboBox1.Value<"" And Not IsNull(me.ComboBox1) And
Me.TextBox1.Value<"" And Not Isnull(Me.TextBox1) then
Cells(3,3) = Me.ComboBox.Value
Cells(3,18) = Me.TextBox1.Value
Else
Msgbox "Please complete form"
End if


---
Message posted from http://www.ExcelForum.com/

Gregor,

Do you mean that you have programmatically added the form via VBA, or did
you design the form in the VBIDE?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gregork" wrote in message
...
I am sorry to be posting on this subject again but I have spent hours
mucking around in help files and google searches with no success. The end
result is confirmation that I don't know what the hell I'm doing in VBA so
I'll describe what I'm trying to do and gratefully accept any advice.

I have inserted a user form (userform1)as a VBA object with the following
objects: combobox1, textbox1, CommandButton1 and CommandButton2.
This is what I want the code to do:
-combobox1-range=sheet!3 A3:A200 and destination cell=C3.
-textbox1- destination cell = R3.
-CommandButton1- Enter data and close the userform.
- CommandButton2-Cancel.

Regards
gregork

Hi georgk

Don't put numbers in place of i.
Just paste the code to the Initialize event of your user form

Dim i as integer
For i = 3 to 200
Me.ComboBox1.AddItem Worksheets("Name of the Worksheet").Cells(i,1)
Next

CommandButton1_Click is the correct one, paste the code for Button 1 in
this event.

For Command2 paste the following code again to its click event :
Unhide Me


---
Message posted from http://www.ExcelForum.com/

Tolgag,

I have noticed that your response often not in the correct place in my NG
reader, for instance this is listed as a response to my point, whereas it
really is in response to Gregor's return to your earlier point. Is this a
'function' of ExcelForum, or are you just responding to latest post, as I
find it misleading.

Just wondered.

Bob

"tolgag " wrote in message
...
Hi georgk

Don't put numbers in place of i.
Just paste the code to the Initialize event of your user form

Dim i as integer
For i = 3 to 200
Me.ComboBox1.AddItem Worksheets("Name of the Worksheet").Cells(i,1)
Next

CommandButton1_Click is the correct one, paste the code for Button 1 in
this event.

For Command2 paste the following code again to its click event :
Unhide Me


---
Message posted from http://www.ExcelForum.com/

Hi Bob,

As you said I was just answering the latest post.
I did'n wanted to create trouble.

Sorr

--
Message posted from http://www.ExcelForum.com

Hi Tolgag (is that your name or just a handle?)

No problem, as I said, I just wondered.

Bob

"tolgag " wrote in message
...
Hi Bob,

As you said I was just answering the latest post.
I did'n wanted to create trouble.

Sorry


---
Message posted from http://www.ExcelForum.com/

Actually, Tolga is my name and the last 'g' is the first letter of my
surname


---
Message posted from http://www.ExcelForum.com/

Thanks, it's nice to put a name to the new frequent posters. Welcome!

Bob

"tolgag " wrote in message
...
Actually, Tolga is my name and the last 'g' is the first letter of my
surname


---
Message posted from http://www.ExcelForum.com/

Thanks Bob, I learn a lot her

--
Message posted from http://www.ExcelForum.com

Many thanks Tolga I eventually got the code in correctly and everything is
working well. You have saved me many hours....thank you. One small thing I
would like to happen is for the user form to disappear when I click the
enter button . Is this possible?
To answer Bob's question : I'm not really sure what VBIDE is let alone
using it for designing forms. What I have been doing is going to Visual
basic editor insert user form view code at the top of the project
explorer window then I paste the code in.
Do you have a suggestion for another method?

Regards
GregorK


"gregork" wrote in message
...
I am sorry to be posting on this subject again but I have spent hours
mucking around in help files and google searches with no success. The end
result is confirmation that I don't know what the hell I'm doing in VBA so
I'll describe what I'm trying to do and gratefully accept any advice.

I have inserted a user form (userform1)as a VBA object with the following
objects: combobox1, textbox1, CommandButton1 and CommandButton2.
This is what I want the code to do:
-combobox1-range=sheet!3 A3:A200 and destination cell=C3.
-textbox1- destination cell = R3.
-CommandButton1- Enter data and close the userform.
- CommandButton2-Cancel.

Regards
gregork

Gregor,

Just add this line at the end of your commandbutton1 click event code

Me.Hide

The VBIDE is the VB Integrated Development Environment, or the Visual Basic
Editor to you<vbg. It is more than just an editor, as it has other
facilities, such as the object browser, debugging aids, etc., so it lays
claim to being an IDE.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gregork" wrote in message
...
Many thanks Tolga I eventually got the code in correctly and everything is
working well. You have saved me many hours....thank you. One small thing I
would like to happen is for the user form to disappear when I click the
enter button . Is this possible?
To answer Bob's question : I'm not really sure what VBIDE is let alone
using it for designing forms. What I have been doing is going to Visual
basic editor insert user form view code at the top of the project
explorer window then I paste the code in.
Do you have a suggestion for another method?

Regards
GregorK


"gregork" wrote in message
...
I am sorry to be posting on this subject again but I have spent hours
mucking around in help files and google searches with no success. The
end
result is confirmation that I don't know what the hell I'm doing in VBA
so
I'll describe what I'm trying to do and gratefully accept any advice.

I have inserted a user form (userform1)as a VBA object with the
following
objects: combobox1, textbox1, CommandButton1 and CommandButton2.
This is what I want the code to do:
-combobox1-range=sheet!3 A3:A200 and destination cell=C3.
-textbox1- destination cell = R3.
-CommandButton1- Enter data and close the userform.
- CommandButton2-Cancel.

Regards
gregork