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

--
Message posted via http://www.officekb.com

[Bob Phillips]

"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

[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

"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
>
>
>

[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?



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

[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

"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
>

[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


[QUOTE=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"

[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)

"AJO" <AJO.22xgo2_1139430605.5476@excelforum-nospam.com> wrote in message
news:AJO.22xgo2_1139430605.5476@excelforum-nospam.com...
>
> 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
>