• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problems with opening a userform

S

Suzanne Peel

New Member
Good Morning/Afternoon/Evening.....

I have a sort of "Catch-22" with opening a userform.

An excel spreadsheet contains the data I want to use. There are 170 odd columns in the spreadsheet so I created a userform to be able to update the spreadsheet in an easier manner.

The form is set up to populate from the spreadsheet - however when the textbox gets it value it triggers the textbox_Change event which runs the code I set up to be able to update the spreadsheet from the form, this in turn triggers the worksheet_Change event which updates the database connected to the spreadsheet.

So basically I copy the cell contents to the userform the userform detects a change and updates the spreadsheet with what it just received and the "change" gets sent to the database to update the data - even though there was no change in the first place .....

I need to determine the column numbers at execution because I don't want to be hardcoding the column numbers only to have to recode when I add or delete a column (which is common).

Any assistance will be gratefully received.

Suzy

I know my programming is awkward but here is the code-

Code: 
Private Sub UserForm_Activate()
Dim ctl As Control
Dim ICOLUMN As Variant
Dim STRFILL As Variant
Dim CNAME As String
Dim rngToFind As Range
Dim i As Long

Call ManCalc
    With Me
        'This will create a vertical scrollbar
        .ScrollBars = fmScrollBarsVertical
        'Change the values of 2 as Per your requirements
        .ScrollHeight = .InsideHeight * 1.5
        .ScrollWidth = .InsideWidth * 9
    End With
    For Each ctl In Me.Controls
        'If Left$(ctl.Name & "1234", 4) = "tbox" Then
        If TypeOf ctl Is MSForms.TextBox Then
            CNAME = Mid$(ctl.Name, 5, 20)
            With ActiveSheet.Range("$2:$2")  ' The cell hold the name of the Textbox
                Set STRFILL = .Find(What:=CNAME, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=False)
            End With
            If Not STRFILL Is Nothing Then  'Not nothing means SOMETHING found
                ICOLUMN = STRFILL.Column
                ctl.Text = ActiveSheet.Cells(ActiveCell.Row, ICOLUMN).Value
            Else
                'MsgBox error routine not essential.
                MsgBox "Error! " & CNAME & " not found." & vbCrLf & _
                        "Processing terminated."
                Call AutoCalc
                Exit Sub
            End If
        End If
    Next ctl
Call AutoCalc
End Sub

Private Sub tboxARno_Change()
  Call All_changes
End Sub

Public Sub All_changes()
Dim CNAME As String
Dim STRFILL As Variant
Dim ICOLUMN As Integer

  CNAME = Mid$(ActiveControl.Name, 5, 20)
  With ActiveSheet.Range("$2:$2")
  Set STRFILL = .Find(What:=CNAME, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False)
  End With
  ICOLUMN = STRFILL.Column
  ActiveSheet.Cells(ActiveCell.Row, ICOLUMN).Value = ActiveControl.Text
End Sub
 
Last edited by a moderator:
Hi Suzanne ,

I am not able to understand whether you have one problem or more than one ; you say that when you show the userform , you are populating all its controls with data from the workbook ; this is triggering the TextBox_Change routine , which then copies the textbox data back to the workbook. Is this correct ?

If so , is this a problem , or do you want the whole process to function in this manner ?

When ever an event procedure is triggered , whatever it may be , if you do not want further triggers to happen , the easy way is to use the Application.EnableEvents = False statement ; when you are done executing the statements which might have triggered this unwanted action , you execute the Application.EnableEvents = True statement.

In your Userform.Activate procedure , you should be enclosing the following statement :

ctl.Text = ActiveSheet.Cells(ActiveCell.Row, ICOLUMN).Value

in the above two statements , to prevent the unwanted triggering of the Textbox_Change event.

Narayan
 
Thank you very much - I thought that I was using the wrong events but using .Application.EnableEvents will work very well.
I will also try your other suggestions - thankyou so very much!!
 
Narayan,
Just confirming - I have added the code, however the textbox_change event still executes - the workbook_change does not execute I am using excel 2013 if that makes any difference.
Code: 
                Application.EnableEvents = False
                ctl.Text = ActiveSheet.Cells(ActiveCell.Row, ICOLUMN).Value
                Application.EnableEvents = True
The form loads up far more quickly now but I would just like to confirm what I have done is correct. I thought I understood that all the events would be halted.
Thanks again!
 
Hi Suzanne ,

Unless the complete code is available , it will be difficult to say from where this is getting triggered ; even your All_changes procedure has a statement which is putting some value into a worksheet cell ; if that statement is not enclosed in the above mentioned two statements , then if that sheet has a Worksheet_Change event procedure , that will get triggered.

If you can upload your file , it will make it very easy to find out where the problem lies.

Narayan
 
Hi Suzy ,

You can send it to me at narayank1026 [at] gmail [dot] com ; but it is late tonight , and I'll be able to respond only tomorrow morning.

Narayan
 
You must log in or register to reply here.
Back
Top