Avoid change event if change made is not by user? 2

[ChrisN]

How can I avoid the change event firing if the change to the textbox is made programatically i.e. automatic. I only want the event to fire if the change is made by the user typing the changes.

Any help is greatly appreciated.

Cheers,
Chris

 

[sunaj]

Here is a suggestion.....

-----------------------------------------------------------
Option Explicit

Dim ProgChange As Boolean
Private Sub ChangeText(Where As TextBox, What As String)
ProgChange = True
Where.Text = What
ProgChange = False
End Sub

Private Sub Command1_Click()
ChangeText Text1, "hello"
End Sub

Private Sub Form_Load()
ProgChange = False
End Sub

Private Sub Text1_Change()
If Not ProgChange Then
MsgBox Text1.Text
End If
End Sub
----------------------------------------------------------- Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[perplexd]

Maybe put the code in KeyPress event? I should think it depends what you are wanting to do...

 

[ChrisN]

Thanks Sunaj, however, I am not too clear on what you mean.

Here is a bit more on what I am trying to do....

When a user selects a row in a spreadsheet control I am using three text boxes are populated with data. If the user changes one of these values the spreadsheet is disabled until a button is clicked and the changes wrote back to the spreadsheet. However, if the user selects a subsequent row then the change event fires and locks the spreadsheet....this becomes a problem if the user has selected the wrong row and can only free it up if the button is clicked which has an impact further down the line when writing to my database as it generates unnecessary transactions.

Hope this gives you a better idea of what I require.

Cheers

 

[sunaj]

I'm afraid I don't understand. The change event that disables the spreadsheet is of the textbox, right?
So why does the spreadsheet lock if the user select a new row? That should not fire the change event of the textbox. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[ChrisN]

When the row is selected the data in the row populates the text boxes.

Chris

 

[sunaj]

ahh of course. Then my solutio should work.
The idea is to set a public bbolean that determines if teh code in the change event should be executed or not; If the user change it -> its executed, but if it is change by code the boolean is set to true and the code is not executed.
So in your case i guess you got some code in the spreadsheet change event that updates the textboxes -right?
Set the boolean to true before this update and to false again after. And make sure that the the value of the boolean is checked before the code in the textbox_change event is executet - do you see? Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[lovell811]

I agree with sunja, setting up your own function to test if the textboxes will change or not is the way to go. That way you can avoid doing it in the keypress like perplexd said, although that could work too, just have to write all the code over again. Hope you get it figured out. ----------------
Joe

 

[ChrisN]

Thanks Sunaj and Joe,

I think I understand where you are coming from and have tried the code but I get a runtime error 13 - type mismatch when I execute it.

Any ideas??

Cheers,
Chris

 

[sunaj]

Did you use the exact code I showed about? If not, post your code, your declarations and indicate where you get the error. (and read faq222-2244) Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[ChrisN]

Sunaj,

Please see my code below;

Dim Disable As Boolean

Private Sub DisableSpread(X As TextBox, Txt As String)
Disable = True
X.Text = Txt
Disable = False
End Sub

Private Sub sprBOMDets_Click(ByVal Col As Long, ByVal Row As Long)

If Col = 0 Then
For i = 1 To 4
sprBOMDets.Col = Col + i
sprBOMDets.Row = Row
If i = 1 Then
DisableSpread txtComponent, sprBOMDets.Text
ElseIf i = 2 Then
DisableSpread txtQty_Use, sprBOMDets.Text
ElseIf i = 3 Then
DisableSpread txtUM, sprBOMDets.Text
ElseIf i = 4 Then
DisableSpread txtNotes, sprBOMDets.Text
End If
Next i
End If

End Sub

 

[ChrisN]

Sunaj,

Please see my code below (missed some off before...sorry);

Dim Disable As Boolean

Private Sub DisableSpread(X As TextBox, Txt As String)
Disable = True
X.Text = Txt
Disable = False
End Sub

Private Sub sprBOMDets_Click(ByVal Col As Long, ByVal Row As Long)
If Col = 0 Then
For i = 1 To 4
sprBOMDets.Col = Col + i
sprBOMDets.Row = Row
If i = 1 Then
DisableSpread txtComponent, sprBOMDets.Text
ElseIf i = 2 Then
DisableSpread txtQty_Use, sprBOMDets.Text
ElseIf i = 3 Then
DisableSpread txtUM, sprBOMDets.Text
ElseIf i = 4 Then
DisableSpread txtNotes, sprBOMDets.Text
End If
Next i
End If
End Sub

Private Sub txtComponent_Change()
If Not Disable Then
If UCase(lblBOMStatus.Caption) Like "AMEND*" Then
sprBOMDets.Enabled = False
Dets_Change = True
End If
End If
End Sub

The run time error occurs at the point highlighted in Red. Many thanks for all your help.

Chris

 

[sunaj]

Hi again,

You are passing a string variable (sprBOMDets.Text) into a textbox object (X) - not possible. The code should read:

DisableSpread txtComponent, sprBOMDets

In this way you'll pass the textbox object (sprBOMDets) to the function that will insert the text

Do you see?
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[johnwm]

Do you need some brackets?

DisableSpread (txtComponent, sprBOMDets.Text) Let me know if this helps

If you are worried about posting, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[ChrisN]

Sunaj,

sprBOMDets.text represents the text contents of a cell within my grid control, and it is this value that I want to pass to the text property of the text box X.

Is there something very simple that I am missing here?

Cheers,
Chris

 

[ChrisN]

Sunaj/anyone else who can assist,

I have tried using your code sample from your first post and this works without any problems. However, as soon as I replace Text1 in this line, ChangeText Text1, "hello" with txtComponent (the name of my text box, I receive the run time error (13 type mismatch).

Any assistance is greatly appreciated.

Chris

 

[sunaj]

Hi Chris,

That does sound a little strange.. If it work with text1 and not with txtcomponent, there but be something different (other than the name.).
For startes you could place a new textbox on the form, check that is works and then rename it to 'txtcomponent'. If you have other changes do them one step at a time to find out where the problem lies.
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[ChrisN]

Hi Sunaj,

Thanks for all your help on this!

It appears that the problem was occuring because I was using UniCode text boxes via the MS Forms 2.0 reference.

Using standard text boxes allows it to work no probs and it also does exactly what I want.

Thanks again,
Chris