Offset from new name based on what's checked in Userform
 

XL2000

I have a sheet that uses a userform to add new names to a list in colA
Early in my code, New_Member is set from userform textbox:
New_Member = UCase$(frmAddMem.tbNewName.Text)

Later my code puts new name at the bottom of current list in ColA:
Set rng = Range(Cells(4, 1), Cells(4, 1).End(xlDown).Offset(1, 0))
rng(rng.Rows.Count) = New_Member

I've added some checkboxes to the userform and renamed them:

cbGH to show name is a group home resident if checked
cbMON to show member attends on Monday if checked
cbTUE to show member attends on Tuesday if checked
and so on through Friday

Cols B:F will house u or l (el) formatted as Wingding to indicate who
attends on a given weekday from a group home or non-group home living
arrangement. I currently enter these manually

Code I want to add:
If cbGH is checked and cbTUE and cbTHU are checked a 'u' will offset from
member name in Cols C and E
If cbGH is unchecked and cbMON, cbWED and cbFRI are checked an 'l' will
offset from member name in Cols B, D, and F

Above are just two scenarios. Any member from any group can attend on any
day, some all 5, some just 1 or any mix thereof.

I want code to look at the state of the checkboxes and act accordingly to
populate B:F with u's or l's or leave any unchecked empty.

I would be willing the change the name property of the boxes to make things
easier, if required.

--
David

Offset from new name based on what's checked in Userform
 

Dim rng as Range
set rng = Cells(4,1).End(xldown).Offset(1,0)
rng.Value = new_member
if cbMON then
With rng.offset(0,1)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbTUE then
With rng.offset(0,2)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbWED then
With rng.offset(0,3)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbTHU then
With rng.offset(0,4)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbFRI then
With rng.offset(0,5)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if

--
Regards,
Tom Ogilvy


"David" wrote in message
...
XL2000

I have a sheet that uses a userform to add new names to a list in colA
Early in my code, New_Member is set from userform textbox:
New_Member = UCase$(frmAddMem.tbNewName.Text)

Later my code puts new name at the bottom of current list in ColA:
Set rng = Range(Cells(4, 1), Cells(4, 1).End(xlDown).Offset(1, 0))
rng(rng.Rows.Count) = New_Member

I've added some checkboxes to the userform and renamed them:

cbGH to show name is a group home resident if checked
cbMON to show member attends on Monday if checked
cbTUE to show member attends on Tuesday if checked
and so on through Friday

Cols B:F will house u or l (el) formatted as Wingding to indicate who
attends on a given weekday from a group home or non-group home living
arrangement. I currently enter these manually

Code I want to add:
If cbGH is checked and cbTUE and cbTHU are checked a 'u' will offset from
member name in Cols C and E
If cbGH is unchecked and cbMON, cbWED and cbFRI are checked an 'l' will
offset from member name in Cols B, D, and F

Above are just two scenarios. Any member from any group can attend on any
day, some all 5, some just 1 or any mix thereof.

I want code to look at the state of the checkboxes and act accordingly to
populate B:F with u's or l's or leave any unchecked empty.

I would be willing the change the name property of the boxes to make
things
easier, if required.

--
David

Offset from new name based on what's checked in Userform
 

Tom Ogilvy wrote

Dim rng as Range
set rng = Cells(4,1).End(xldown).Offset(1,0)
rng.Value = new_member
if cbMON then
With rng.offset(0,1)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbTUE then
With rng.offset(0,2)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbWED then
With rng.offset(0,3)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbTHU then
With rng.offset(0,4)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if
if cbFRI then
With rng.offset(0,5)
.Value = iif(cbGH,"u","l")
.Font.Name = "Wingdings"
End With
end if


As written, it puts the name at the bottom of the list, but no u's or
l's, and later my line:
rng.Resize(, cols).Sort Key1:=Range("A1"), Order1:=xlAscending
fails to sort.

I changed all rng.offsets to rng(rng.Rows.Count).Offset to match existing
code and sort worked, but still no u's or l's :{

If it helps, here is entire original code:

Sub Add_Member()
Dim rng As Range, New_Member As String
Dim cols As Long
frmAddMem.Show
New_Member = UCase$(frmAddMem.tbNewName.Text)
If New_Member = "" Then Unload frmAddMem: Exit Sub
Unload frmAddMem
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(4, 1), Cells(4, 1).End(xlDown).Offset(1, 0))
rng(rng.Rows.Count) = New_Member
rng.Resize(, cols).Sort Key1:=Range("A1"), Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

--
David

Offset from new name based on what's checked in Userform
 

Tom Ogilvy wrote

<snip

Tada!!

Sub Add_Member()
Dim rng As Range, New_Member As String, Maxed As String
Dim cols As Long
frmAddMem.Show
New_Member = UCase$(frmAddMem.tbNewName.Text)
If New_Member = "" Then Unload frmAddMem: Exit Sub
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(4, 1), Cells(4, 1).End(xlDown).Offset(1, 0))
rng(rng.Rows.Count) = New_Member
If frmAddMem.cbMON Then rng(rng.Rows.Count).Offset(0, 1).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbTUE Then rng(rng.Rows.Count).Offset(0, 2).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbWED Then rng(rng.Rows.Count).Offset(0, 3).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbTHU Then rng(rng.Rows.Count).Offset(0, 4).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbFRI Then rng(rng.Rows.Count).Offset(0, 5).Value = IIf
(frmAddMem.cbGH, "u", "l")
Unload frmAddMem
rng.Resize(, cols).Sort Key1:=Range("A1"), Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

For some reason I had to precede all cb's with frmAddMem. and wait to
Unload frmAddMem until they were processed.

Thanks for leading the way!

--
David

Offset from new name based on what's checked in Userform
 

Sub Add_Member()
Dim rng As Range, New_Member As String, Maxed As String
Dim cols As Long
With frmAddMem
.Show
New_Member = UCase$(.tbNewName.Text)
If New_Member = "" Then Unload frmAddMem: Exit Sub
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Cells(4, 1).End(xlDown).Offset(1, 0)
rng = New_Member
If .cbMON Then rng.Offset(0, 1).Value = IIf(.cbGH, "u", "l")
If .cbTUE Then rng.Offset(0, 2).Value = IIf(.cbGH, "u", "l")
If .cbWED Then rng.Offset(0, 3).Value = IIf(.cbGH, "u", "l")
If .cbTHU Then rng.Offset(0, 4).Value = IIf(.cbGH, "u", "l")
If .cbFRI Then rng.Offset(0, 5).Value = IIf(.cbGH, "u", "l")
Unload frmAddMem
Range(cells(4,1),rng).Resize(, cols).Sort _
Key1:=Range("A1"), Order1:=xlAscending
Application.ScreenUpdating = True
End With
End Sub

when you show the userform, the code stops execution until the userform is
hidden. Code not in the userform module must be qualified with the userform
name. Using WITH makes this much more compact.

--
Regards,
Tom Ogilvy



"David" wrote in message
...
Tom Ogilvy wrote

<snip

Tada!!

Sub Add_Member()
Dim rng As Range, New_Member As String, Maxed As String
Dim cols As Long
frmAddMem.Show
New_Member = UCase$(frmAddMem.tbNewName.Text)
If New_Member = "" Then Unload frmAddMem: Exit Sub
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(4, 1), Cells(4, 1).End(xlDown).Offset(1, 0))
rng(rng.Rows.Count) = New_Member
If frmAddMem.cbMON Then rng(rng.Rows.Count).Offset(0, 1).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbTUE Then rng(rng.Rows.Count).Offset(0, 2).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbWED Then rng(rng.Rows.Count).Offset(0, 3).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbTHU Then rng(rng.Rows.Count).Offset(0, 4).Value = IIf
(frmAddMem.cbGH, "u", "l")
If frmAddMem.cbFRI Then rng(rng.Rows.Count).Offset(0, 5).Value = IIf
(frmAddMem.cbGH, "u", "l")
Unload frmAddMem
rng.Resize(, cols).Sort Key1:=Range("A1"), Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

For some reason I had to precede all cb's with frmAddMem. and wait to
Unload frmAddMem until they were processed.

Thanks for leading the way!

--
David

Offset from new name based on what's checked in Userform
 

Tom Ogilvy wrote

when you show the userform, the code stops execution until the
userform is hidden. Code not in the userform module must be qualified
with the userform name. Using WITH makes this much more compact.

I like it. Thanks.
I mistakenly thought that my cmdDone_Click line Me.Hide in the userform
module accomplished that.

Added bonus: the modified/compacted 'Set rng = ...' line. I'll incorporate
it in similar Add_Member modules in other workbooks.

--
David