Three userform questions

[Chris Cred via OfficeKB.com]

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

 

[Bob Phillips]

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

 

[Toppers]

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

 

[Chris Cred via OfficeKB.com]

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?

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
[quoted text clipped - 52 lines]

Trim(Me.ComboBox2.Value) = "" And _
Trim(Me.ComboBox3.Value) = "" Then

 

[Toppers]

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

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?

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

 

[AJO]

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

 

[Bob Phillips]

Go to Tools>Customize 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)