Posts by IanDunnett

Re: Unable to get Points property of the series class.

Hi,

What is the value of i when you get the error (just hover the cursor over the (i) part to find this out when in debug mode).

That should help identify the problem.

Thanks,
Ian

Re: Referencing User Selected Workbooks

It might be that although you've opened the workbook it is not yet active so you're previous line is not selecting a sheet wihtin the workbook you've just opened.

Try either inserting a line to activate the workbook first;

Workbooks(csvFN).Activate
csvSheetName = ActiveSheet.Name 
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")

or accessing the active sheet in the workbook you've just opened;

csvSheetName = Workbooks(csvFN).ActiveSheet.Name 
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")

Cheers,
Ian

Re: Referencing User Selected Workbooks

I think by using the reference method you are it's best to use normal referencing rather than the R1C1 variety.

Try changing the line where the error occurs to;

Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")

Hope that helps,
Ian

Re: Next Available Cell Row

Corrected code below;

Sub Macro11() 
     
    Dim Wbk As Workbook 
    Dim WbkToCopyFrom As String 
    Dim Response1 As VbMsgBoxResult 
     
    Response1 = vbNo 
     
    For Each Wbk In Application.Workbooks 
         
        If InStr(1, LCase(Wbk.Name), "stat") > 0 Then 
            Response1 = MsgBox("Is " & Wbk.Name & " the workbook you wish to copy from?", vbYesNo, "Select Workbook") 
             
            If Response1 = vbYes Then 
            WbkToCopyFrom = Wbk.Name
            Exit For 
            End If
             
        End If 
         
         
         
    Next Wbk 
     
    WbkToCopyFrom = Wbk.Name 
     
    Range("A4:N4").Select 
    Selection.copy 
     
    Dim EmpRow As Range 
    Dim PrinttoRow As Integer 
     
    Set EmpRow = Workbooks("1.xls").Sheets("Front Sheet").Columns(1).Find("", _ 
    After:=Workbooks("1.xls").Sheets("Front Sheet").Cells(1, 1), LookAt:=xlWhole) 
     
    PrinttoRow = EmpRow.Row 
     
     
End Sub

Cheers,
Ian

Re: Fill First Blank Cell In Multiple Rows

This should be a simple way of achieving what you're after;

Option Explicit


Sub AddBrackets()


Dim i As Integer
Dim j As Integer


i = 1


Do
j = 0


Do
j = j + 1
Loop Until IsEmpty(ActiveSheet.Cells(i, j))


ActiveSheet.Cells(i, j).Value = ")"


i = i + 1
Loop Until IsEmpty(ActiveSheet.Cells(i, 1))


End Sub

Hope that helps,
Ian

Re: Report Of Employees By Date, Holidays & Sick Days

There's a slight error in the code working out the number of hours...it's using the date rather than the info on the employee row...it just so happens that the formula applied to a 2008 date = 8 hours but applied to a 2009 date = 9 hours which is where the error was coming from.

Updated section below;

x = x + 1 
            AbRec(0, x) = KeyMap(1, d) 
            AbRec(1, x) = Sheets("Data").Cells(1, b).Value 
            AbRec(2, x) = Sheets("Data").Cells(1, b).Value 
            AbRec(3, x) = Int(Right(Sheets("Data").Cells(EmpRow, b).Value, _ 
            Len(Sheets("Data").Cells(EmpRow, b).Value) - 1)) 
            AbRec(4, x) = d

Cheers,
Ian

Re: Next Available Cell Row

Thanks. It's a structure I use quite a lot when needing to select something from a set that isn't too big.

It saves having to write a userform with a listbox...

It can be applied to workbooks, sheets, items within a table, filtering options...anything really where you need to select one / multiple items from a set.

Re: Next Available Cell Row

I'm assuming the common part of the filename is 'stat' here;

try something like;

Dim Wbk As Workbook
Dim WbkToCopyFrom As String
Dim Response1 As VbMsgBoxResult


Response1 = vbNo


For Each Wbk In Application.Workbooks


If InStr(1, LCase(Wbk.Name), "stat") > 0 Then
Response1 = MsgBox("Is " & Wbk.Name & " the workbook you wish to copy from?", vbYesNo, "Select Workbook")


If Response1 = vbYes Then Exit For


End If


Next Wbk


WbkToCopyFrom = Wbk.Name

This will search thorugh all the open workbooks and let you select the one you want to copy from by using a messagebox to ask whichis the right one.

Then just use 'WbkToCopyFrom' as the variable everytime you need to refer to the source workbook.

Hope that's clear and helps,
Ian

Re: Next Available Cell Row

Something like;

Dim EmpRow As Range
Dim PrinttoRow As Integer


Set EmpRow = Workbooks("workbookname").Sheets("sheetname").Columns(1).Find("", _ 
After:=Workbooks("workbookname").Sheets("sheetname").Cells(1, 1), LookAt:=xlWhole)


PrinttoRow = EmpRow.Row

You'll need to replace sheetname and workbookname with the name of the sheet and workbook you're searching on and this assumes the workbook is open and that the first blank in column A is where you want to start adding to.

Cheers,
Ian

Re: Formula Too Big For Nesting

If you wanted a quick fix to this just create an extra column on each sheet with a formula in that gives you Year and Month in the format YYYYMM then you could easily maintain the data you need with pivot tables...

Formula would be (assuming row 2 with date in column D);
=YEAR(D2)&TEXT(MONTH(D2),"00")

Cheers,
Ian

Re: Macro To Add VLOOKUP Formula To lookup Another Workbook

I think you just need to separate out the variable in the string...

try;

'Search for comments last week from last week's file
Workbooks(this1).Activate 
With Range("P2") 
    Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).Select 
    Selection.FormulaR1C1 = _ 
    "=VLOOKUP(RC[-8],'[" & that1 & "]Raw'!C9:C18,10,0)" 
    Selection.Value = Selection.Value 
End With 
Windows(that1).Activate 
ActiveWorkbook.Close

Hope that does it.
Ian

Re: Colour Textbox

Assuming your userform is called userform1, the textbox is called textbox1 and the checkbox is called checkbox1 then you'll need something like this in the code section of the userform which will fire any time the checkbox value is changed;

Private Sub CheckBox1_Change()


With UserForm1


If .CheckBox1.Value = True Then
.TextBox1.BackColor = &H80000011
Else:
.TextBox1.BackColor = &H80000005
End If


End With


End Sub

Using the Locked and Enabled properties you could also stop users from beinmg able to alter the contents of the textbox.

Hope that's useful,
Ian

Re: Delete Rows That Dont Have Certain Text

Hi Cameo,

Try this, code notated to help explain;

Sub RemoveNonMARs()


Dim WSht As Worksheet 'to scan through sheets
Dim a As Integer ' to count row we are looking at


For Each WSht In Application.Worksheets 'scan through each sheet in turn
With WSht


a = 2 'assume row 1 is title row so start on row 2
Do


If InStr(1, UCase(.Cells(a, 1).Value), "MAR") > 0 Then 'if "MAR" is in the cell somewhere
a = a + 1 'move to next row
Else:
.Rows(a).Delete 'otherwise delete the row
End If


Loop Until IsEmpty(.Cells(a, 1)) 'keep going until you cvome to an empty row


End With
Next WSht 'move to next worksheet


End Sub

Cheers,
Ian

Re: Conditional Formatting For N/a Fields

I would go for using the iserror formula...

=IF(ISBLANK(A7),"",IF(ISERROR(VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)),"Missing Account","Match Found"))

Cheers,
Ian

ADDED BY ADMIN
=VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)
In 1 column (eg "B") and;
=IF(ISNA(B1),"Missing Account","Match Found")
Hide column B

Re: Report Of Employees By Date, Holidays & Sick Days

Hi MJB123,

I've quickly knocked up something that should get you somewhere near where you need to be...

The sheet (attached) now has a userform to select the employee to run the report for, a command button on the data sheet to start up the macro, a report template page to copy the format for each individual and a key sheet which relies on the following;

All codes for possible absence types listed.
All codes are only one letter long.
The order of the codes is the same as they appear in the columns on the report.

here's the code;

Sub EmployeeReport_Click()


Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim x As Integer
Dim y As Integer


Dim EmpRow As Integer
Dim KeyMap() As String
ReDim KeyMap(1, 99)


Dim AbRec() As Variant
ReDim AbRec(4, 365)


Dim NewSht As Worksheet


c = 1
Do
KeyMap(0, c) = Sheets("Key").Cells(c, 1).Value
KeyMap(1, c) = Sheets("Key").Cells(c, 2).Value
c = c + 1
Loop Until IsEmpty(Sheets("Key").Cells(c, 1))


ReDim Preserve KeyMap(1, c - 1)


Load EmpSelect


a = 2


With EmpSelect.EmpCmb
.Clear


Do
.AddItem Sheets("Data").Cells(a, 1).Value
a = a + 1
Loop Until IsEmpty(Sheets("Data").Cells(a, 1))


End With


EmpSelect.Show


EmpRow = EmpSelect.EmpCmb.ListIndex + 2


b = 2
Do


If Sheets("Data").Cells(EmpRow, b).Value <> "" Then


For d = 1 To UBound(KeyMap, 2)
If KeyMap(0, d) = Left(Sheets("Data").Cells(EmpRow, b).Value, 1) Then Exit For
Next d


x = x + 1
AbRec(0, x) = KeyMap(1, d)
AbRec(1, x) = Sheets("Data").Cells(1, b).Value
AbRec(2, x) = Sheets("Data").Cells(1, b).Value
AbRec(3, x) = Int(Right(Sheets("Data").Cells(1, b).Value, _
Len(Sheets("Data").Cells(EmpRow, b).Value) - 1))
AbRec(4, x) = d


End If


b = b + 1
Loop Until IsEmpty(Sheets("Data").Cells(1, b))


Set NewSht = ActiveWorkbook.Sheets.Add


With NewSht


Sheets("Report Template").Cells.Copy Destination:=.Cells
.Cells(3, 2).Value = EmpSelect.EmpCmb.Value


For y = 1 To x
.Cells(5 + y, 2).Value = AbRec(0, y)
.Cells(5 + y, 3).Value = AbRec(1, y)
.Cells(5 + y, 4).Value = AbRec(2, y)
.Cells(5 + y, 5 + AbRec(4, y)).Value = AbRec(3, y)
Next y


.Rows(5 + y & ":370").Delete


.Name = "Absence - " & .Cells(3, 2).Value


z = 6
Do


If ((.Cells(z, 2).Value = .Cells(z + 1, 2).Value) And _
(.Cells(z, 4).Value + 1 = .Cells(z + 1, 3).Value)) Then


.Cells(z, 4).Value = .Cells(z + 1, 4).Value


For e = 6 To UBound(KeyMap, 2) + 5
If .Cells(z, e) <> "" Then .Cells(z, e).Value = .Cells(z, e).Value + .Cells(z + 1, e).Value
Next e


.Rows(z + 1).Delete


Else:
z = z + 1
End If


Loop Until IsEmpty(.Cells(z, 2).Value)


End With


Application.Calculate
Unload EmpSelect


End Sub

and on the userform

Private Sub OKBtn_Click()


If EmpSelect.EmpCmb.ListIndex <> -1 Then
EmpSelect.Hide
Else:
MsgBox ("Please select an employee from the list!")
End If


End Sub

First it summarises each nonn-blank column for the employee into a data record, then it puts that on a fresh template.
Finally it then scans thorugh what it has just done and combines any records on consecutive days of the same absence type.

Hope that Helps!
Feel free to let me know any issues

Cheers,
Ian

Re: Oleobject, Trigger Event, Not Firing

Sorry I obviously read your post a bit quickly last time and tried to come up with too simple a solution.

I think the key here is to not delte the buttons at all. I would just create an array that after stripping out the numbers, instead of deleting them, just finds the first number not already taken and uses that as the name for the new command button...

That way you don't get any name clashes and you don't have to delete any which is what is probably causing the link to the macro to break down.

Something like;

Sub A_A_Utility_ButtonCreationForHOME_PopulateViewEditItemButton()
   
Dim M As Long
Dim I As Long
Dim Name As String
Dim NName As String
Dim NumItems As Long
Dim NumUsed(999) As Boolean


Dim x As Integer
Dim y As Integer


For x = 1 To UBound(NumUsed)
NumUsed(x) = False
Next x
    
Range("A3").Value = "=COUNTA(A4:A1000)" 'Count...
NumItems = Range("A3").Value
Range("A3").Value = ""
     
'=============Delete Buttons=================
For Each OLEObject In Sheets("HOME").OLEObjects


If Left(OLEObject.Name, 10) = "ItemButton" Then


Name = Right(OLEObject.Name, Len(OLEObject.Name) - 10) ' Take the letter to the right of those 10 letters as call them the variable (string) "Name"


NumUsed(CInt(Name)) = True


End If


Next OLEObject


'=============Then Add the Buttons Back========= (prevents double creation of buttons)


Range("A" & 2 * I + 3).Select


Dim ItemButtonObj As OLEObject, N%
Set ItemButtonObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left + 2, Top:=ActiveCell.Top - 4, _
Width:=1.93 * ActiveCell.Width, Height:=18)


For y = 1 To 999
If NumUsed(y) = False Then Exit For
Next y


ItemButtonObj.Name = "ItemButton" & y


ItemButtonObj.Object.Caption = "View/Edit Entries"
ItemButtonObj.Object.Font.Size = 8


End Sub

That's not tested so it may not work 100% but it should get you in the right direction.

Cheers,
Ian

Re: Create Chart With Dynamic Named Ranges

I think the problem is that you are Dim'ing rng and val as ranges, then declaring them as strings...

As .XValues and .Values as members of the .Series object are variants they can be either a string or a range, but not both.

Make sense?
Ian

Re: Change Caption Of Label In Form

Hi ANdy,

Maybe you need to send an example because I don't think I'm getting why this is a problem...

You can change the caption of a label at anytime as long as you have loaded the userform the label sits on (you don't even need to hide the form to do so).

But if you want it to change just use the same line of code, just before you show the form for the second time, e.g.

Sub ChangeLabelTwice()


Load UserForm1


With UserForm1


.Label1.Caption = "title you want first"


.Show 'show for the first time


'do some other stuff here as code will restart here when form is exited


.Label1.Caption = "title you want second"


.Show ' show form with calendar on for second time, with new title


Unload UserForm1


End Sub

Hope that's better.
Ian

Re: Oleobject, Trigger Event, Not Firing

Hi There,

I've encountered similar problems before.

It's normally that the EnableEvents property isn't set to TRUE. Although this is the default for Excel the property can sometimes be upset by breaking your code halfway through or when your code hits an error.

My way around it when I'm writing code is always to have a command button that fires;

Application.EnableEvents = True

Which I click before testing anything with triggers, and then I'm finished you can add the same line of code as the first line of code against all your command buttons in the workbook.

That way you can guarantee, as long as the user has clicked a command button first, that the events should fire every time.

I know that's not a specific solution to your problem but I hope it gets you in the right direction.

Hope that helps,
Ian

Re: Change Caption Of Label In Form

Hi Andy,

This should be pretty easy...

If you want to change the caption on a userform that the calendar control is inserted on (say the userform is called UserForm1) then use;

UserForm1.Caption = "Whatever the title should be"

The other way would be to insert a label in the userform and have that changing value (if Label was called Label1);

UserForm1.Label1.Caption = "Whatever the title should be"

Or you could have a msgbox pop up before the form to say which date they should enter...there's lots of ways...

Hope that helps. Let me know if you need something more specific.
Ian