Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
Actually, Tolga is my name and the last 'g' is the first letter of my
surname --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Code Required
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another VB Code Required | Excel Discussion (Misc queries) | |||
VB Code Required | Excel Discussion (Misc queries) | |||
Macro Code required | Excel Discussion (Misc queries) | |||
Simple Count formula required | Excel Discussion (Misc queries) | |||
simple validation formula required | Excel Discussion (Misc queries) |