Make Sub Private temporarily disabled

S

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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
solblurgh said:
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?
Click to expand...


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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Kyle123 said:
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
Click to expand...

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: 5
Upvote 0
dmt32 said:
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
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
No
solblurgh said:
Hello,

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

No, you need to create 2 class modules, called AnyTextBox and TbHandler, then put the respective code in each of those
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Why isn't this code working
Replies
5
Views
289
amoverton2
A
B
  • Question
VBA Private Sub for all sheets
2
Replies
15
Views
751
BrainDiseasee
B
M
  • Question
UserForms Private Sub names
Replies
5
Views
449
tfurnivall
T
A
  • Question
possible calculation in listbox for debit , credit when search based on textbox ?
Replies
0
Views
280
abdelfattah
A
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
306
pure vito
P

Forum statistics

Threads
1,214,414
Messages
6,119,373
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top