Userforms

[rhino4eva]

I am attempting to teach myself how to do userforms
I can draw them and get the code behind them but I am struggling to pull everything together . If I describe what I need could someone please help out

I have 7 tick boxes on a USER FROM lined up named CEA,HAV,ENT,MENIGO,PHM,PCP,RNASEp and one command button called GO

the boxes can be ticked in any combination and when go is pressed I would like a list on sheet 1 of the names ticked

I know I nearly there but just need a nudge over the hill

 

[rhino4eva]

Re: More help with userforms please

I have a working userform but it's not pretty. A bank of 6 if thens.i keep thinking an array might tidy it up ie one for the column a tests and one for the column s ones

 

[My Aswer Is This]

Re: More help with userforms please

OK

Here try this:
I set myself up a new Userform and tested this out.
I saved my file this time in case you have more changes.

Private Sub Go_Click()
'Modified 12-13-17 1:25 PM EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowd = Cells(Rows.Count, "D").End(xlUp).Row + 1
    For Each xcontrol In Me.Controls
        If TypeName(xcontrol) = "CheckBox" Then
            If xcontrol.Value = True Then
                If xcontrol.Caption = "HAV" Or xcontrol.Caption = "ENT" Or xcontrol.Caption = "MENIGO" Then
                    Cells(Lastrow, 1).Value = xcontrol.Caption
                    xcontrol.Value = False
                    Lastrow = Lastrow + 1
                Else
                    Cells(Lastrowd, "D").Value = xcontrol.Caption
                    xcontrol.Value = False
                    Lastrowd = Lastrowd + 1
                End If
                
            End If
        End If
    Next xcontrol
End Sub

 

[rhino4eva]

Re: More help with userforms please

I saw what you did with the " HAv ENT MENINGO"
and modded to close any gap for the other three

do I need to dim Lastrowd

Private Sub Go_Click()

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowd = Cells(Rows.Count, "D").End(xlUp).Row + 1
For Each xcontrol In Me.Controls
If TypeName(xcontrol) = "CheckBox" Then
If xcontrol.Value = True Then
If xcontrol.Caption = "HAV" Or xcontrol.Caption = "ENT" Or xcontrol.Caption = "MENIGO" Then
Cells(Lastrow, 1).Value = xcontrol.Caption
xcontrol.Value = False
Lastrow = Lastrow + 1
​

Else IF xcontrol.Caption= "phm" or xcontrol.Caption = "PCP" Or xcontrol.Caption = "RNASEp" THEN​

Cells(Lastrowd, "D").Value = xcontrol.Caption
xcontrol.Value = False
Lastrowd = Lastrowd + 1
End If

End If
End If
Next xcontrol
End Sub​

 

[My Aswer Is This]

Re: More help with userforms please

Sure. I missed that. It will work without it but do ahead and do that.

Why did you add the Elseif?
Do you have other checkboxes you did not want included?
And in your original post you said:
CEA,HAV,ENT,MENIGO,PHM,PCP,RNASEp

But now I see you have changed those.

I saw what you did with the " HAv ENT MENINGO"
and modded to close any gap for the other three

do I need to dim Lastrowd

Private Sub Go_Click()

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowd = Cells(Rows.Count, "D").End(xlUp).Row + 1
For Each xcontrol In Me.Controls
If TypeName(xcontrol) = "CheckBox" Then
If xcontrol.Value = True Then
If xcontrol.Caption = "HAV" Or xcontrol.Caption = "ENT" Or xcontrol.Caption = "MENIGO" Then
Cells(Lastrow, 1).Value = xcontrol.Caption
xcontrol.Value = False
Lastrow = Lastrow + 1
​

Else IF xcontrol.Caption= "phm" or xcontrol.Caption = "PCP" Or xcontrol.Caption = "RNASEp" THEN​

Cells(Lastrowd, "D").Value = xcontrol.Caption
xcontrol.Value = False
Lastrowd = Lastrowd + 1
End If

End If
End If
Next xcontrol
End Sub​

 

[rhino4eva]

Re: More help with userforms please

I am really grateful for your patience and knowledge. You are right about learning , I feel like I have enough to get by but new commands set me in a tail spin

 

[My Aswer Is This]

Re: More help with userforms please

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

I am really grateful for your patience and knowledge. You are right about learning , I feel like I have enough to get by but new commands set me in a tail spin

 

[My Aswer Is This]

Re: More help with userforms please

You never did say why you thought you needed to use Elseif.

I am really grateful for your patience and knowledge. You are right about learning , I feel like I have enough to get by but new commands set me in a tail spin

 

[rhino4eva]

Re: More help with userforms please

Your else to catch the d column seemed to be a non specific catch all . So I thought I would need an "if" to list those hense the else if

 

[My Aswer Is This]

Re: More help with userforms please

Only if there were other checkboxes you wanted to exclude.
So we said if for the few but did not need if for the others.
Sort of like:

For every car in my garage
If type= "Chevy" or Type="Ford" Then
Paint Blue

Else
Paint Red
End if
 Next

So all cars that are not Chevy or Ford will be painted Red

Your else to catch the d column seemed to be a non specific catch all . So I thought I would need an "if" to list those hense the else if

 

[rhino4eva]

Ok I get what you mean...... Don't over complicate and go for the line of least resistsnce