Simple Code Required

[gregork]

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

 

[tolgag]

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

 

[gregork]

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

 

[Bob Phillips]

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)

 

[tolgag]

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

 

[Bob Phillips]

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]

Hi Bob,

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

Sorr

 

[Bob Phillips]

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

No problem, as I said, I just wondered.

Bob

 

[tolgag]

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

 

[Bob Phillips]

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

Bob

 

[tolgag]

Thanks Bob, I learn a lot her

 

[gregork]

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

 

[Bob Phillips]

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)