Textboxes - more problems

[Glaswegian]

Hello again

I'm not having a very good week with Texboxes.

Following some kind help from Killian, my Textboxes now re-size when required.

However, I need to clear the text and re-align the texboxes and make this as simple as possible for the users (bit like the users really).

The textboxes use the Change Event to re-size as text is added. I created a button and code to clear the TBs, re-align and re-size them. That part works fine. It's the Application.DisplayAlerts bit that is causing the problem. When running the code to clear the TBs, the code is also running the TB Change Event. This produces an error because the TB does not have the focus. Also because of this, the LineCount Property produces an error as well - because the TB does not have the focus. I cannot see a reason why the Change Event would run. There is no other code in the workbook (no modules either) as this is just a mock up I'm using for testing etc.

As always, I'd appreciate any help or suggestions.

Regards

[Ken Puls]

Hi Iain,

Do you need to just disable the change event to solve the issue? (I haven't downloaded your file to look.)

If so, just throw this in in the beginning:

Application.enableevents = false

Don't forget to set it back to true at the end though.

HTH,

[Norie]

Ken

I did download the file and Iain has turned off/on events.

[Ken Puls]

Huh, well that is weird. I have no idea why it is still firing the change events, but I think this could solve your issue.

As the first line of each of the change events, throw this in:

If TextBox#.Value = vbNullString Then Exit Sub

Obviousley, you'll need to update textbox# to Textbox1, Textbox 2, etc... (the number of the change procedure). It looks like the clearing is successful, but that's what's firing the event.

Seems like a funny way to solve the issue though, but I think it will work for you.

HTH,

[Glaswegian]

Ken

Thanks for your reply.

The file is in the office so I'll try out your suggestion on Monday.

I agree, though that it is rather strange and I must admit I wouldn't have thought of adding that line - nice one!

Just because I'm stubborn I'd still like to know why the Change Events are firing?

Regards

[Ken Puls]

Actually, so would I!

To me, it seems that your structure is set up correctly:

EnableEvents = false
Loop through and change textbox properties
EnableEvents = true

Why it would trigger the change event when the textbox is changed is beyond me. My first thought would be that events got turned on by mistake somewhere, but I don't see where that is happening either... I don't get it!

You can't think that OLEObjects are special and ignore that command, can you?

[Glaswegian]

You can't think that OLEObjects are special and ignore that command, can you?

Interesting question - I didn't think they were - but hey, you never know!!

Actually, just thinking back to this afternoon when I was playing around with this - I stepped through every line of code - and I do remember the events not firing only if the Textboxes had already been cleared of all text. That puzzled me but I couldn't come up with an answer (and still can't ).

Regards

[acw]

Hi

I think the problem is that the Activex control event (ie the textbox change) is not affected by the application.enableevents property

One way I think I have found to beat the problem is to generate a public global variable and use it to control the textbox event.

Public DoIt as boolean

At the start of each of the textbox change events, put in a surrounding if statement
If Not doit Then
...
End If

As DoIt will default to false, then it will enable the normal changes to be applied.

In the ClearTBs macro add the lines to control the global variable

For Each ole In ActiveSheet.OLEObjects
    If ole.ProgId = "Forms.TextBox.1" Then
    DoIt = True '// added to control the text box change event
    ole.Visible = True
        With ole.Object
            .AutoSize = False
            .Text = ""
        End With
    End If
    ole.Visible = True
    DoIt = False
Next ole

In the quick bit of texting I did, it seemed to work. But you would want to spend more time testing.....

I may also pay to consider actively controlling the variable with a workbook_activate, or workbook_open event. Whichever way you go (if this works that is), document fully as it would be interesting to try to debug...


HTH

Tony

[Ken Puls]

Hi Tony,

It's an interesting approach. I haven't played with it, but I can see what you mean. Good thinking!

I wasn't aware of ActiveX controls not being affected by Application.EnableEvents though. Are you positive on that? Things would lead me to believe that you're right, but it does seem strange...

[acw]

Ken

No, I'm not positive. It was a hunch based on what was happening. I did some searching on the Microsoft site, but couldn't come up with anything that said "Activex controls are not affected by Application.enableevents...". But it did lead me to thinking that this might be the case.

I can't make up my mind if it makes sense or not. Or if it is one of those things that users just seem to do but would never occur to someone making the code!!!


Tony

[Glaswegian]

Tony

Thank you! Your solution works well

Ken

Thanks also for your input on this.

I must admit that I did not consider the fact that ActiveX controls may not be affected by Events. I don't think I've ever come across a similar situation so therefore never considered it. Something else to file away in the old grey matter!

Think I can now mark this thread solved.

Thanks again to all.

Regards