Big Ask? Loading UserForms and then using the Check Box

[Guest]

I am very new to VBA and am trying to do the following, I have created the
Userform and check boxes but that is as far as i can go.

When a ceratin a cell e.g A1 goes to = red then a userform will pop up with
a list of check boxes e.g Nexus, G2, Swift, Crest if Nexus and Crest check
box is ticked then the word nexus will appear A2 and Crest in A3. Or if G2
and Swift were checked then G2 would be in cell A2 and Swift in cell C3.

Many Thanks

 

[Dave Peterson]

Changing the format of a cell isn't enough to make excel notice the change.

But you could tie showing your userform to a change in a column (or a range of
columns).

If you want to try it, rightclick on the worksheet tab that should have this
behavior and choose view code. Then paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:L1")) Is Nothing Then
Exit Sub
End If

UserForm1.Show

End Sub



I created a userform (userform1) with a bunch of checkboxes and two
commandbuttons (ok/cancel). I put the values in the captions of each checkbox.

Option Explicit
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim ctrl As Control

iCtr = 0
Application.EnableEvents = False
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Object.Value = True Then
iCtr = iCtr + 1
ActiveCell.Offset(iCtr, 0).Value = ctrl.Object.Caption
End If
End If
Next ctrl
Application.EnableEvents = True

Unload Me

End Sub