Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
Hello Everyone,
I just created a userform and it works great thanks to Toppers help. I just have two questions that might be simple for some of you to answer. 1: Is there a way to create a link in a cell that will launch the userform when the user is ready to use it? If so, could I get some info on that? I'm stuck n how to let the user interact with it. 2: In one of my comboboxs I have a range using the RowSource property. Is there a way to eliminate a few of the cells in that range so they don't appear in the combobox? FYI, the ones I want to eliminate from the combox still need to appear on the worksheet thought. They are spaces and sections titles. 3. Using the code below I have it setup where combobox1 must have data in it for the user to click the "Add" button. What would I have to alter in this code so combobox 2 and combobox3 also checks for a course number before they add data? Thanks in advance, Chris Code: Private Sub cmdAdd_Click() rowno = Columns(1).Find(Trim(ComboBox1.Value)).Row Range("C" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox2.Value Range("D" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox3.Value 'check for a course number If Trim(Me.ComboBox1.Value) = "" Then Me.ComboBox1.SetFocus MsgBox "Okay, this is not Rocket Science. Please select the course number and give it another shot. FYI, the questions get harder." Exit Sub End If 'clear the data Me.ComboBox1.Value = "" Me.ComboBox2.Value = "" Me.ComboBox3.Value = "" Me.ComboBox1.SetFocus End Sub -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
"Chris Cred via OfficeKB.com" <u17747@uwe wrote in message news:5abb1659b7359@uwe... Hello Everyone, I just created a userform and it works great thanks to Toppers help. I just have two questions that might be simple for some of you to answer. 1: Is there a way to create a link in a cell that will launch the userform when the user is ready to use it? If so, could I get some info on that? I'm stuck n how to let the user interact with it. This works if you double-click cell H10 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("H10")) Is Nothing Then userform1.Show End If End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code 2: In one of my comboboxs I have a range using the RowSource property. Is there a way to eliminate a few of the cells in that range so they don't appear in the combobox? FYI, the ones I want to eliminate from the combox still need to appear on the worksheet thought. They are spaces and sections titles. The only way would be to extract the required data from the original range, maybe using Advanced Filter, and point the combo at that new range. 3. Using the code below I have it setup where combobox1 must have data in it for the user to click the "Add" button. What would I have to alter in this code so combobox 2 and combobox3 also checks for a course number before they add data? Thanks in advance, Chris Code: Private Sub cmdAdd_Click() rowno = Columns(1).Find(Trim(ComboBox1.Value)).Row Range("C" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox2.Value Range("D" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox3.Value Isn't that obvious? If Trim(Me.ComboBox1.Value) = "" And _ Trim(Me.ComboBox2.Value) = "" And _ Trim(Me.ComboBox3.Value) = "" Then |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
Chris,
Some alternatives to Bob's solutions: (1) Use a button instead of a cell and call macro e.g. Sub DisplayForm() UserForm1.Show End Sub Go to FORMS toolbar, select Button control and then "Assign Macro"; in my example "DisplayForm" 2. Combobox3 has data from non-contiguous cells Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!a2:a10" ' add data from named range ComboBox2.RowSource = "Data2" ' add data fron non-contiguous cells ...... For Each cell In Range("c2:c4, c7:c10") ComboBox3.AddItem cell.Value Next cell End Sub 3. Check all CBs for data entered; change Errmsg array as required. Private Sub CommandButton1_Click() Dim ErrorFound As Boolean Errmsg = Array("Please enter course number", "Please enter ...combobox2..", "Please Enter ..combobox3.") ErrorFound = False ' Check if there has been an entry in each combobox For i = 1 To 3 If Controls("combobox" & i).ListIndex = -1 Then MsgBox Errmsg(i - 1) ErrorFound = True End If Next i If ErrorFound Then Exit Sub '..continue ... End Sub "Bob Phillips" wrote: "Chris Cred via OfficeKB.com" <u17747@uwe wrote in message news:5abb1659b7359@uwe... Hello Everyone, I just created a userform and it works great thanks to Toppers help. I just have two questions that might be simple for some of you to answer. 1: Is there a way to create a link in a cell that will launch the userform when the user is ready to use it? If so, could I get some info on that? I'm stuck n how to let the user interact with it. This works if you double-click cell H10 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("H10")) Is Nothing Then userform1.Show End If End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code 2: In one of my comboboxs I have a range using the RowSource property. Is there a way to eliminate a few of the cells in that range so they don't appear in the combobox? FYI, the ones I want to eliminate from the combox still need to appear on the worksheet thought. They are spaces and sections titles. The only way would be to extract the required data from the original range, maybe using Advanced Filter, and point the combo at that new range. 3. Using the code below I have it setup where combobox1 must have data in it for the user to click the "Add" button. What would I have to alter in this code so combobox 2 and combobox3 also checks for a course number before they add data? Thanks in advance, Chris Code: Private Sub cmdAdd_Click() rowno = Columns(1).Find(Trim(ComboBox1.Value)).Row Range("C" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox2.Value Range("D" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox3.Value Isn't that obvious? If Trim(Me.ComboBox1.Value) = "" And _ Trim(Me.ComboBox2.Value) = "" And _ Trim(Me.ComboBox3.Value) = "" Then |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
Toppers, thank you once again!
Okay, I finished my project and everything works falwlessly. However, when I protected the cells that contain formulas, I cannot pass data to the worksheet. I did, however, unprotect columns A, B, C and D since C and D is where the data will be passed to and A and B are allowed text changes by the user. Any suggestions? Toppers wrote: Chris, Some alternatives to Bob's solutions: (1) Use a button instead of a cell and call macro e.g. Sub DisplayForm() UserForm1.Show End Sub Go to FORMS toolbar, select Button control and then "Assign Macro"; in my example "DisplayForm" 2. Combobox3 has data from non-contiguous cells Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!a2:a10" ' add data from named range ComboBox2.RowSource = "Data2" ' add data fron non-contiguous cells ...... For Each cell In Range("c2:c4, c7:c10") ComboBox3.AddItem cell.Value Next cell End Sub 3. Check all CBs for data entered; change Errmsg array as required. Private Sub CommandButton1_Click() Dim ErrorFound As Boolean Errmsg = Array("Please enter course number", "Please enter ...combobox2..", "Please Enter ..combobox3.") ErrorFound = False ' Check if there has been an entry in each combobox For i = 1 To 3 If Controls("combobox" & i).ListIndex = -1 Then MsgBox Errmsg(i - 1) ErrorFound = True End If Next i If ErrorFound Then Exit Sub '..continue ... End Sub Hello Everyone, [quoted text clipped - 52 lines] Trim(Me.ComboBox2.Value) = "" And _ Trim(Me.ComboBox3.Value) = "" Then -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
Chris,
You can protect/unprotect a sheet using code; something like the code below but make sure you remember the password!: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) ActiveSheet.Unprotect Password:="password" Range("C10") = "=10.0*" & CLng(TextBox1.Value) & "" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End Sub "Chris Cred via OfficeKB.com" wrote: Toppers, thank you once again! Okay, I finished my project and everything works falwlessly. However, when I protected the cells that contain formulas, I cannot pass data to the worksheet. I did, however, unprotect columns A, B, C and D since C and D is where the data will be passed to and A and B are allowed text changes by the user. Any suggestions? Toppers wrote: Chris, Some alternatives to Bob's solutions: (1) Use a button instead of a cell and call macro e.g. Sub DisplayForm() UserForm1.Show End Sub Go to FORMS toolbar, select Button control and then "Assign Macro"; in my example "DisplayForm" 2. Combobox3 has data from non-contiguous cells Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!a2:a10" ' add data from named range ComboBox2.RowSource = "Data2" ' add data fron non-contiguous cells ...... For Each cell In Range("c2:c4, c7:c10") ComboBox3.AddItem cell.Value Next cell End Sub 3. Check all CBs for data entered; change Errmsg array as required. Private Sub CommandButton1_Click() Dim ErrorFound As Boolean Errmsg = Array("Please enter course number", "Please enter ...combobox2..", "Please Enter ..combobox3.") ErrorFound = False ' Check if there has been an entry in each combobox For i = 1 To 3 If Controls("combobox" & i).ListIndex = -1 Then MsgBox Errmsg(i - 1) ErrorFound = True End If Next i If ErrorFound Then Exit Sub '..continue ... End Sub Hello Everyone, [quoted text clipped - 52 lines] Trim(Me.ComboBox2.Value) = "" And _ Trim(Me.ComboBox3.Value) = "" Then -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
I have a quick question about this alternative. You say go to the Forms toolbar and select Button control. Where exactly is the Forms toolbar? I can't seem to figure out what you're referring to. Thanks Toppers Wrote: Chris, Some alternatives to Bob's solutions: (1) Use a button instead of a cell and call macro e.g. Sub DisplayForm() UserForm1.Show End Sub Go to FORMS toolbar, select Button control and then "Assign Macro"; in my example "DisplayForm" -- AJO ------------------------------------------------------------------------ AJO's Profile: http://www.excelforum.com/member.php...o&userid=31286 View this thread: http://www.excelforum.com/showthread...hreadid=503758 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three userform questions
Go to ToolsCustomize and select the Toolbars tab, and check Forms in there,
that is the Forms toolbar. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "AJO" wrote in message ... I have a quick question about this alternative. You say go to the Forms toolbar and select Button control. Where exactly is the Forms toolbar? I can't seem to figure out what you're referring to. Thanks Toppers Wrote: Chris, Some alternatives to Bob's solutions: (1) Use a button instead of a cell and call macro e.g. Sub DisplayForm() UserForm1.Show End Sub Go to FORMS toolbar, select Button control and then "Assign Macro"; in my example "DisplayForm" -- AJO ------------------------------------------------------------------------ AJO's Profile: http://www.excelforum.com/member.php...o&userid=31286 View this thread: http://www.excelforum.com/showthread...hreadid=503758 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA userform questionS | Excel Discussion (Misc queries) | |||
Two UserForm Questions | Excel Programming | |||
Further Userform questions | Excel Programming | |||
Userform Questions | Excel Programming | |||
newbie with userform questions | Excel Programming |