Make Sub Private temporarily disabled

solblurgh

New Member
Joined
Nov 2, 2016
Messages
6
Hello MrExcel,

I have a userform (userform1) that has 3 text boxes (TextBox1, 2, and 3) and a command button (CommandButton1). The idea is I want to
Sample File
1. Call Userform1 with a button that I created in Sheet1 using Developer -> Insert -> Form Controls
2. Type number in each of the textboxes, and the number appears in cell A1, A2, and A3 in Sheet1

I made this Private Sub:
VBA Code:
Private Sub FilltheCell()

For i = 1 To 3
Sheets("Sheet1").Range("A" & i).Value = Controls("TextBox" & i).Value
Next i

End Sub

and

VBA Code:
Private Sub TextBox1_Change()

Call FilltheCell

End Sub
Private Sub TextBox2_Change()

Call FilltheCell

End Sub
Private Sub TextBox3_Change()

Call FilltheCell

End Sub

The first Sub copies the number from textboxes to the corresponding cells, while the other 3 Subs call the first Sub whenever I change the number inside the textbox.

⭐Question #1 - It does work, but is this quickest way to do it? In my actual file I have more than 100 textboxes and it seems like I need to put "Call FilltheCell" more than 100 times


3. When I close the workbook and reopen it, the number in the textboxes disappear, so I write a Private Sub to recall the number from the cells back into the textboxes:

VBA Code:
Private Sub CommandButton1_Click()

For i = 1 To 3
Controls("TextBox" & i).Value = Sheets("Sheet1").Range("A" & i).Value
Next i

End Sub

This code does not work, and what actually happen is; it copies the number from the first iteration (Sheet1, Cell A1 to TextBox1) only, and it erases the number in the next iterations.

What I think actually happen is:
  1. Click CommandButton1, for i =1, Excel finds the number in sheet Sheet1, Cell A1, and copy it to TextBox1.
  2. By doing this, it technically "changes" the number in TextBox1, from 0 to whatever number in Cell A1.
  3. When TextBox1 figures is "changed", it sets off another command: Private Sub TextBox1_Change(), which calls for Private Sub FilltheCell()
  4. This command will find all figures in all TextBoxes, and copy it to the cell. Initially the textboxes are empty, thus it will copy 0 to the cell in sheet Sheet1
  5. Command in Step 1 will find the next i, which is i =2, and now cell A2 in sheet Sheet1 is 0, thus it will copy 0 to my TextBox2.
⭐Question #2 - Is there any other way that I can "disable" Step 2 and 3, and successfully running the loop?

I was thinking is it possible to temporarily disable Sub FilltheCell when I run CommandButton1 but I do not know how to. Is there a way to do such thing?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

solblurgh

New Member
Joined
Nov 2, 2016
Messages
6
Hello MrExcel,

I have a userform (userform1) that has 3 text boxes (TextBox1, 2, and 3) and a command button (CommandButton1). The idea is I want to
Sample File
1. Call Userform1 with a button that I created in Sheet1 using Developer -> Insert -> Form Controls
2. Type number in each of the textboxes, and the number appears in cell A1, A2, and A3 in Sheet1

I made this Private Sub:
VBA Code:
Private Sub FilltheCell()

For i = 1 To 3
Sheets("Sheet1").Range("A" & i).Value = Controls("TextBox" & i).Value
Next i

End Sub

and

VBA Code:
Private Sub TextBox1_Change()

Call FilltheCell

End Sub
Private Sub TextBox2_Change()

Call FilltheCell

End Sub
Private Sub TextBox3_Change()

Call FilltheCell

End Sub

The first Sub copies the number from textboxes to the corresponding cells, while the other 3 Subs call the first Sub whenever I change the number inside the textbox.

⭐Question #1 - It does work, but is this quickest way to do it? In my actual file I have more than 100 textboxes and it seems like I need to put "Call FilltheCell" more than 100 times


3. When I close the workbook and reopen it, the number in the textboxes disappear, so I write a Private Sub to recall the number from the cells back into the textboxes:

VBA Code:
Private Sub CommandButton1_Click()

For i = 1 To 3
Controls("TextBox" & i).Value = Sheets("Sheet1").Range("A" & i).Value
Next i

End Sub

This code does not work, and what actually happen is; it copies the number from the first iteration (Sheet1, Cell A1 to TextBox1) only, and it erases the number in the next iterations.

What I think actually happen is:
  1. Click CommandButton1, for i =1, Excel finds the number in sheet Sheet1, Cell A1, and copy it to TextBox1.
  2. By doing this, it technically "changes" the number in TextBox1, from 0 to whatever number in Cell A1.
  3. When TextBox1 figures is "changed", it sets off another command: Private Sub TextBox1_Change(), which calls for Private Sub FilltheCell()
  4. This command will find all figures in all TextBoxes, and copy it to the cell. Initially the textboxes are empty, thus it will copy 0 to the cell in sheet Sheet1
  5. Command in Step 1 will find the next i, which is i =2, and now cell A2 in sheet Sheet1 is 0, thus it will copy 0 to my TextBox2.
⭐Question #2 - Is there any other way that I can "disable" Step 2 and 3, and successfully running the loop?

I was thinking is it possible to temporarily disable Sub FilltheCell when I run CommandButton1 but I do not know how to. Is there a way to do such thing?


I do not know how to edit my initial post, but I want to let you guys know that I figured Question #2 out. I amend the Sub FilltheCell to:

VBA Code:
Private Sub FilltheCell()
For i = 1 To 3
If Trim(Controls("TextBox" & i).Value) <> "" Then
Sheets("Sheet1").Range("A" & i).Value = Controls("TextBox" & i).Value
Else
End If
Next i
End Sub

Then I created a Private Sub named Refill:

VBA Code:
Private Sub Refill()
For i = 1 To 3
Controls("TextBox" & i).Value = Sheets("Sheet1").Range("A" & i).Value
Next i
End Sub

and amend Sub CommandButton1_Click to:

VBA Code:
Private Sub CommandButton1_Click()
Call Refill
End Sub


If you guys can think of any other way or easier way to get the same result, you are most welcome. Thank you in advance.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,173
Office Version
  1. 2019
Platform
  1. Windows
What is happening is as your code updates each textbox, the event for that control is triggered and runs the code associated with it. In a workbook, you have the ability to turn events off but no such facility exists for userform events so you have to create your own to prevent recursion.
Also, to avoid creating multiple events for large number of textboxes you would create a class for the controls.

Having said all that, rather than update each range in real time as you make changes to each textbox, consider updating the ranges in on go by calling your code from the button you have on your userform.

Place ALL code unaltered in your userforms code page

VBA Code:
Private Sub CommandButton1_Click()
    FilltheCell
End Sub

Private Sub UserForm_Initialize()
    FilltheCell True
End Sub


Sub FilltheCell(Optional ByVal LastInput As Boolean)
    Dim i As Integer
    Dim ws As Worksheet
    
    Set ws = Worksheets("Sheet1")
    For i = 1 To 3
        With Me.Controls("TextBox" & i)
            If LastInput Then
'load record from range
                .Value = ws.Range("A" & i).Value
            Else
                ws.Cells(i, 1).Value = .Value
            End If
        End With
    Next
End Sub

When you open the form, the values should populate your textboxes
Pressing the commandbutton should update the ranges with any changes

Dave
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
I'd probably do something like this:
VBA Code:
Option Explicit

Private WithEvents CtrArray As AnyTextBox
Private tbs As Collection

Private Sub CtrArray_AnyTextBoxChanged(ByVal tb As MSForms.TextBox)

    Sheets("sheet1").Range("a" & Replace(tb.Name, "TextBox", "")).Value = tb.Value
   
End Sub

Private Sub UserForm_Initialize()

    Dim ctl As Control
    Dim tb  As TbHandler
    Dim i   As Long
   
    Set tbs = New Collection
   
    Set CtrArray = New AnyTextBox
   
    For i = 1 To 3
        Controls("TextBox" & i).Value = Sheets("Sheet1").Range("A" & i).Value
    Next i
   
   
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            Set tb = New TbHandler
            Set tb.EventHandler = CtrArray
            Set tb.tb = ctl
            tbs.Add tb
        End If
    Next ctl
   
End Sub

Class AnyTextBox
VBA Code:
Public Event AnyTextBoxChanged(ByVal tb As MSForms.TextBox)
Public Sub Changed(ByVal tb As MSForms.TextBox)
    RaiseEvent AnyTextBoxChanged(tb)
End Sub

Class TbHandler
VBA Code:
Public WithEvents tb As MSForms.TextBox
Public EventHandler As AnyTextBox
Private Sub Tb_Change()
    EventHandler.Changed tb
End Sub

The AnyTextBox step is superfluous, but I like to keep the code together in the userform
 

solblurgh

New Member
Joined
Nov 2, 2016
Messages
6

ADVERTISEMENT

Thank you both for the responses, I will try both and see which one works out better for me.
 

solblurgh

New Member
Joined
Nov 2, 2016
Messages
6
I'd probably do something like this:
VBA Code:
Option Explicit

Private WithEvents CtrArray As AnyTextBox
Private tbs As Collection

Private Sub CtrArray_AnyTextBoxChanged(ByVal tb As MSForms.TextBox)

    Sheets("sheet1").Range("a" & Replace(tb.Name, "TextBox", "")).Value = tb.Value
  
End Sub

Private Sub UserForm_Initialize()

    Dim ctl As Control
    Dim tb  As TbHandler
    Dim i   As Long
  
    Set tbs = New Collection
  
    Set CtrArray = New AnyTextBox
  
    For i = 1 To 3
        Controls("TextBox" & i).Value = Sheets("Sheet1").Range("A" & i).Value
    Next i
  
  
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            Set tb = New TbHandler
            Set tb.EventHandler = CtrArray
            Set tb.tb = ctl
            tbs.Add tb
        End If
    Next ctl
  
End Sub

Class AnyTextBox
VBA Code:
Public Event AnyTextBoxChanged(ByVal tb As MSForms.TextBox)
Public Sub Changed(ByVal tb As MSForms.TextBox)
    RaiseEvent AnyTextBoxChanged(tb)
End Sub

Class TbHandler
VBA Code:
Public WithEvents tb As MSForms.TextBox
Public EventHandler As AnyTextBox
Private Sub Tb_Change()
    EventHandler.Changed tb
End Sub

The AnyTextBox step is superfluous, but I like to keep the code together in the userform

Hello,

Does it mean to be pasted inside the Userform code? I got this error when I debug it:
 

Attachments

  • kyle123.PNG
    kyle123.PNG
    45.4 KB · Views: 2

solblurgh

New Member
Joined
Nov 2, 2016
Messages
6

ADVERTISEMENT

What is happening is as your code updates each textbox, the event for that control is triggered and runs the code associated with it. In a workbook, you have the ability to turn events off but no such facility exists for userform events so you have to create your own to prevent recursion.
Also, to avoid creating multiple events for large number of textboxes you would create a class for the controls.

Having said all that, rather than update each range in real time as you make changes to each textbox, consider updating the ranges in on go by calling your code from the button you have on your userform.

Place ALL code unaltered in your userforms code page

VBA Code:
Private Sub CommandButton1_Click()
    FilltheCell
End Sub

Private Sub UserForm_Initialize()
    FilltheCell True
End Sub


Sub FilltheCell(Optional ByVal LastInput As Boolean)
    Dim i As Integer
    Dim ws As Worksheet
   
    Set ws = Worksheets("Sheet1")
    For i = 1 To 3
        With Me.Controls("TextBox" & i)
            If LastInput Then
'load record from range
                .Value = ws.Range("A" & i).Value
            Else
                ws.Cells(i, 1).Value = .Value
            End If
        End With
    Next
End Sub

When you open the form, the values should populate your textboxes
Pressing the commandbutton should update the ranges with any changes

Dave

Thank you so much, I love the idea of populating the userform without clicking the CommandButton. It looks neater than having to click the button everytime I re-open the worksheet. However I still need to click the button everytime I change the figure in the textbox (i.e while working on the userform). I will find a workaround to it.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
You can control a Userform's events by introducing a module wide boolean variable, DisableMyEvents.
Your event code could test for that variable before exicuting. And you would manually control it whenever you changed a control's value that you didin't want to cascade.

VBA Code:
' in userform code module

Dim DisableMyEvents as Boolean

Private Sub TextBox1_Change()
    If DisableMyEvents Then Exit Sub
    ' your text box 1 code
End Sub

Private Sub TextBox2_Change()
    If DisableMyEvents Then Exit Sub
    ' your text box 2 code
End Sub

'  more events

Private Sub CommandButton1_Click()
    
    DisableMyEvents = True

    For i = 1 to 2
        Me.Controls("TextBox" & i).Value = i
    Next i

    DisableMyEvents = False

End Sub
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
No
Hello,

Does it mean to be pasted inside the Userform code? I got this error when I debug it:

No, you need to create 2 class modules, called AnyTextBox and TbHandler, then put the respective code in each of those
 

Watch MrExcel Video

Forum statistics

Threads
1,113,823
Messages
5,544,537
Members
410,618
Latest member
bigz
Top