Why wont it do?

[thorpyuk]

Dim bfr As Long
bfr = ComboBox1.Value
ComboBox1.Value = bfr: Exit Sub

how come this gives me a "cant change property" error!?

The code is inside my combobox change event...

 

[Norie]

Right you are using Outlook and that's the problem - a missing reference.

This is a common occurence when you try and develop over multiple versions.

Later/earlier versions might have different libraries versions.

Theoretically you could go round checking references but there is another, perhaps better method - late binding.

This is a technique used when you don't want to refer specifically to a library.

For example for the click event of CommandButton2 on UserForm4 you have this section of code.

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
    Set olApp = New Outlook.Application
    Set olApt = olApp.CreateItem(olAppointmentItem)
    With olApt
        .Start = varDate + TimeValue("09:30:00")
        .End = .Start + TimeValue("02:00:00")
        .Subject = UserForm4.Label23.Caption & " " & UserForm4.Label24.Caption & " " & UserForm4.Label38.Caption
        .Location = "My desk"
        .Body = "key date for: " & ComboBox4.Text
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 60
        .ReminderSet = True
        '.Display
        .Save
    End With
    Set olApt = Nothing
    Set olApp = Nothing

Now this requires you to have a reference to the Outlook library.

I've only glanced at the code and don't have time to go in to details on how it would look using late-binding.

But one of the first things would be to dim olApp, olApt as just 'Object'.

And you'll have to change this to use Create(or Get)Object.

    Set olApp = New Outlook.Application

I can't remember the exact syntax but it would be something like Set olApp = CreateOject(...).

Also in this section of code you are using constants from the Outlook library like olAppointmentItem.

If you are using late-binding you need to replace it with it's actual value or create a constant with that name/value yourself.

 

[thorpyuk]

This late binding sounds like it might be the long term solution....

I'll take a proper look tomorrow guys.

Norie - thanks for taking the time to have a look at my project :D

 

[Norie]

I'm pretty sure late-binding will help but I don't think it's going to be the a/long-term solution for the whole project.

Like I said I've only had a quick look but a few alarm bells started ringing -344 Goto statements.

And there's something weird going on in the UnhideSheets sub.

The obvious problem is the use of Sheet for the worksheet - not a good idea, that's an (obscure) VBA property method.

But when changed to sh more trouble, something to do with the Projects worksheet.

I can manually set it to be visible in the VBE but for some reason the code baulks , even when all you want to do is get the name.

What worksheets do you want to hide/unhide anyway?

Perhaps you should look into using Select Case?

Select Case sh.Name
      Case "Prompt","_Buttonz","Changes", "Temp", "Adhoc"
        ' hide listed worksheets
         sh.Visible = xlSheetVeryHidden
      Case Else
        ' make all other sheets visible
         sh.Visible = xlSheetVisible
End Select

 

[thorpyuk]

Hi Norie,

Hmmm.... well with those hide/unhide routines, maybe use:

blnhaltchange = true before using any code. (and then blnhaltchange = false after)

There's quite a lot of code in worksheet activate events which may be causing the problems. Most of those will exit the event if blnhaltchange = true.

I'll have a play with your select case method, maybe this'll be better!

 

[Norie]

There's quite a lot of code in Activate events.

There's quite a lot of code full stop.

I analzyed it using an add-in and as far as I can tell there was over 16,000 lines of code in the whole project.

And there was a lot of unneeded code repition.

For example in command button 1 on userform1 there are approx 150 lines of code and you keep on repeating this.

  If TextBox2.Text <> "neil" Then '<=== Replace with a real password
            ans = MsgBox("Invalid password. Try again?", 4, "Invalid password")
                If ans = vbYes Then
                    TextBox2.Text = ""
                    TextBox2.SetFocus
                    Exit Sub
                Else: Exit Sub
                End If

And the only change appears to be the password.

 

[thorpyuk]

Bah :<

It's because i'm better at 'hacking a solution' than refining one!

For eg, I need 4 new tyres on my car :> The tyres i have on currently are legal, and they work, but they aren't great, look crap and don't hold the road as well as they should do :D

What would you suggest as a better way of handling that particular example?

(all it does is it's the code that allows or deny's users access.... you're right, password & username check, but the code is the same.)

 

[Norie]

thorpy

I might have a look at that part but one of the main problems with 'fixing' it is the way it's currently written.

I really think you need to step back, grab a pen/pencil and a notepad.

Then write down, from scratch, in words what you want to do.

Start by detailing the major goals/steps, then split those down to substeps, then split those down...

Once you've done that start thinking about the code.

PS When I was taught this approach it was called working from 'top-down'. ie you start with the big concepts and work down from them.

 

[thorpyuk]

Hiya,

I'm having a stab at speeding up some small bits of the project...

Is it possible to search backwards? EG:

        Set Cell = .Find(What:=ComboBox1.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)

This code appears in the Initialise event of userform7....

OR, can i add the items backwards by altering

.AddItem (Cell.EntireRow.Cells(1, 1).Value)

?

I then have a separate piece of code to sort it.
It's stored in date order anyway on my sheet, so if i populated it from the bottom of the sheet to the top, i'd not need to sort it, hence speeding up my code :D

 

[Norie]

thorpy

Since it's still not clear what you are actually doing I'm afraid I don't know the answer.

And I've looked at the file you sent me, which didn't shed any light on this or anything else.

In fact I'm afraid to say I just gave up after the 100th error message.

And the only way I could see to get past the errors was to comment out the code, which kind of defeats the purpose of trying to debug it.

 

[RoryA]

You can use:

SearchDirection:=xlPrevious

to search backwards, or you can check each piece of data as you add it to the listbox. If your source data is sorted, you should probably filter the sheet to get the data you want, then loop through each cell in order.