Range problem (2003 SP3)

[steveh]

Morning all

I have a user form that in part uses this piece of code

Code: Select all

Dim ICell As Range
cboLeaveType.Clear
For Each oCell In ThisWorkbook.Names("LeaveTypes").RefersToRange
cboLeaveType.AddItem CStr(oCell.Value)
Next
Me.Calendar1 = Date
End Sub

The Range is Horizontal and represents leave types H, S, M, P, T, A, U & B and is currently accessed via a combo box on the user form but I think I will need to change it to a text box if I am to achieve the following.

if I changed the range to Hh, Ss etc. to represent full and half days could I somehow add a 'Case' statement to the above so that it adds either a capital or lower case letter to the worksheet?

[HansV]

What do you mean by "adds either a capital or lower case letter to the worksheet"? Add upper or lower case at random? Or according to some kind of logic - if so, what logic?

[steveh]

What do you mean by "adds either a capital or lower case letter to the worksheet"? Add upper or lower case at random? Or according to some kind of logic - if so, what logic?

Morning Hans

Currently a user can select any one of the Capital letters from the Combo box and once a submit button is pressed the holiday type along with dates from and to, employee's name and department are added to the worksheet.

I know need to allow for 1/2 days but don't want to make the range any bigger than it is because it will look and become messy. I thought that by adding a text box instead of the Combo box the user could manually enter an H or a h, or a S or a s it would, when submitted add the letter in the correct Case onto the worksheet so that my counif formula's could pick up the half and full days.

I hope that makes more sense

[HansV]

I would keep the combo box and leave the range as it is. I'd add a check box chkHalfDay labeled "Half Day" to the userform.

In the code behind the submit button, use the check box to enter the correct letter, for example (this is just air code)

Code: Select all

  Dim strLetter As String
  strLetter = Me.cboLeaveType
  If Me.chkHalfDay = True Then
    strLetter = LCase(strLetter)
  End If
  Range("appropriatecell") = strLetter

[steveh]

I would keep the combo box and leave the range as it is. I'd add a check box chkHalfDay labeled "Half Day" to the userform.

In the code behind the submit button, use the check box to enter the correct letter, for example (this is just air code)

Code: Select all

  Dim strLetter As String
  strLetter = Me.cboLeaveType
  If Me.chkHalfDay = True Then
    strLetter = LCase(strLetter)
  End If
  Range("appropriatecell") = strLetter

Hi Hans

Thank you for this. I have added the check box and put the code behind it but I am not sure how to change the "appropriatecell" as the user form finds the next blank space in column A,B,C,D and F. The code below is all of the code that sits behind the user form and the part below is what I believe is the appropriate cell range (Column D (Irow 4) is the letter for the leave type))

Code: Select all

With ws
.Cells(Irow, 1).Value = Me.cboName
.Cells(Irow, 2).Value = CDate(Me.txtDateFrom.Value)
.Cells(Irow, 3).Value = CDate(Me.txtDateTo.Value)
.Cells(Irow, 4).Value = Me.cboLeaveType.Value
.Cells(Irow, 6).Value = Me.TxtAuthorised
End With

Code: Select all

Private Sub Chkhalfday_Click()
Dim strLetter As String
  strLetter = Me.cboLeaveType
  If Me.Chkhalfday = True Then
    strLetter = LCase(strLetter)
  End If
 Range("appropriatecell") = strLetter
End Sub

Private Sub cmdAddLeave_Click()
Dim Irow As Long
Dim Iname As Long
Dim ws As Worksheet
Set ws = Worksheets("Planner")

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Worksheets("Planner").Unprotect Password:="LetMeIn"

'find first empty row in db

Irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Iname = Me.cboName.ListIndex

'Check for a name

If Trim(Me.cboName.Value) = "" Then
MsgBox "Please enter an employees name"
    Exit Sub
End If

If Trim(Me.cboLeaveType.Value) = "" Then
MsgBox "Please enter the purpose of the leave"
    Exit Sub
End If

'Copy the data to the datafields

With ws
.Cells(Irow, 1).Value = Me.cboName
.Cells(Irow, 2).Value = CDate(Me.txtDateFrom.Value)
.Cells(Irow, 3).Value = CDate(Me.txtDateTo.Value)
.Cells(Irow, 4).Value = Me.cboLeaveType.Value
.Cells(Irow, 6).Value = Me.TxtAuthorised
End With

'Clear the data

Me.cboName.Value = ""
Me.txtDateFrom.Value = ""
Me.txtDateTo.Value = ""
Me.cboLeaveType.Value = ""
Me.TxtAuthorised.Value = ""
Me.cboName.SetFocus

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

'Worksheets("Planner").Protect Password:="LetMeIn"

End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub cmdDateFrom_Click()
Me.txtDateFrom = Format(Me.Calendar1, "dd/mm/yyyy")
End Sub
Private Sub cmdDateTo_Click()
 Me.txtDateTo = Format(Me.Calendar1, "dd/mm/yyyy")
End Sub

Private Sub lblDateFrom_Click()

End Sub

Private Sub UserForm_Initialize()
    
Dim oCell As Range
cboName.Clear
For Each oCell In ThisWorkbook.Names("PDPStaff").RefersToRange
cboName.AddItem CStr(oCell.Value)
Next

Dim ICell As Range
cboLeaveType.Clear
For Each oCell In ThisWorkbook.Names("LeaveTypes").RefersToRange
cboLeaveType.AddItem CStr(oCell.Value)
Next
Me.Calendar1 = Date
End Sub

[HansV]

You don't want anything to happen when the user ticks or clears the check box! The check box is meant to be used by cmdAddLeave to determine what exactly to enter in column D. So you should remove the Chkhalfday_Click event procedure.

Please rename the check box to chkHalfDay - it won't make a iota of difference, but it looks better than Chkhalfday...

Here is the code for cmdAddLeave_Click, with the code that I posted added, and properly indented:

Code: Select all

Private Sub cmdAddLeave_Click()
  Dim iRow As Long
  Dim iName As Long
  Dim ws As Worksheet
  Dim strLetter As String

  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False

  Set ws = Worksheets("Planner")
  ws.Unprotect Password:="LetMeIn"

  'Find first empty row in db
  iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  iName = Me.cboName.ListIndex

  'Check for a name
  If Trim(Me.cboName.Value) = "" Then
    MsgBox "Please enter an employees name"
    Exit Sub
  End If

  If Trim(Me.cboLeaveType.Value) = "" Then
    MsgBox "Please enter the purpose of the leave"
    Exit Sub
  End If

  'Copy the data to the datafields
  With ws
    .Cells(iRow, 1).Value = Me.cboName
    .Cells(iRow, 2).Value = CDate(Me.txtDateFrom.Value)
    .Cells(iRow, 3).Value = CDate(Me.txtDateTo.Value)
    strLetter = Me.cboLeaveType.Value
    If Me.chkHalfDay = True Then
      strLetter = LCase(strLetter)
    End If
    .Cells(iRow, 4).Value = strLetter
    .Cells(iRow, 6).Value = Me.txtAuthorised
  End With

  'Clear the data
  Me.cboName.Value = ""
  Me.txtDateFrom.Value = ""
  Me.txtDateTo.Value = ""
  Me.cboLeaveType.Value = ""
  Me.txtAuthorised.Value = ""
  Me.cboName.SetFocus

  'ws.Protect Password:="pdpleave" ' why a different password here?

  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

[steveh]

You don't want anything to happen when the user ticks or clears the check box! The check box is meant to be used by cmdAddLeave to determine what exactly to enter in column D. So you should remove the Chkhalfday_Click event procedure.

Please rename the check box to chkHalfDay - it won't make a iota of difference, but it looks better than Chkhalfday...

Here is the code for cmdAddLeave_Click, with the code that I posted added, and properly indented:

Hi Hans

That puts the lower case in perfect, thank you. The passwords were different because I had meant to change them when they were compromised because somebody left the workbook open ( me!)

I have though now run into another problem because I forgot that the formula that actually updates the calendar cells refers to the range and therefore leaves it as a capital

=IF(WEEKDAY(N$6,2)>5,"",IF(ISNUMBER(MATCH(N$6,PublicHoliday,0)),"PH",INDEX(LeaveTypes,SUMPRODUCT(($H7=SNames)*(SFrom<=N$6)*(STo>=N$6)*SType))))

Is there anything you could suggest that I could change in the formula to reflect the upper or lower case selection?

[HansV]

Without seeing the workbook that's hard to say.

[steveh]

Without seeing the workbook that's hard to say.

Cheers Hans

I was afraid of that because I was going to post a stripped down version in the first post and then (and now) no matter how I strip the WB down to meet the lounge limits it stops working.

I will try again when I get home from work later