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:
and
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:
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:
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 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:
- Click CommandButton1, for i =1, Excel finds the number in sheet Sheet1, Cell A1, and copy it to TextBox1.
- By doing this, it technically "changes" the number in TextBox1, from 0 to whatever number in Cell A1.
- When TextBox1 figures is "changed", it sets off another command: Private Sub TextBox1_Change(), which calls for Private Sub FilltheCell()
- 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
- 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.
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?