VBA Textbox event to call private userform function - excel

I'm trying to create an userform that have a textbox field and, at runtime, i'll be able to add how many more boxes I need.
For now, I've created a button that does that, but I want it to automatically create the new field just pressing the '+' key while in the box.
I found out that I can create a custom class module to handle all the new boxes I'm creating, but I'm not able to make this class call the function that creates the next ones.
How do I say to my program "every time I hit the +, you create a new box"?
What I've got so far is this
clsObjHandler class module:
Public WithEvents txtBoxCustom As MSForms.TextBox
Private Sub txtBoxCustom_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 43 Then
createNewBox
KeyAscii = 0
End If
End Sub
UserForm module:
Option Explicit
Dim colTbxs As Collection
Public Function createNewBox()
Dim newField As Control
Dim i As Integer
Dim clsObject As clsObjHandler
i = Me.Frame1.Controls.count
Set newField = Me.Frame1.Controls.Add("Forms.TextBox.1", "SESPO" & i)
newField.name = "SESPO" & i
newField.Left = 12
newField.Top = insertLineBtn.Top + 18
insertLineBtn.Top = insertLineBtn.Top + 18
insertPoForm.Height = insertPoForm.Height + 18
Frame1.Height = Frame1.Height + 18
insertPosBtn.Top = insertPosBtn.Top + 18
newField.SetFocus
Set clsObject = New clsObjHandler
Set clsObject.txtBoxCustom = newField
colTbxs.Add clsObject
Set clsObject = Nothing
End Sub

I solved using the Application.Run on the class module. Dont know if it's the best approach, but it worked. Like this:
Public WithEvents txtBoxCustom As MSForms.TextBox
Private Sub txtBoxCustom_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 43 Then
Application.Run UserForm.createNewBox
KeyAscii = 0
End If
End Sub

Related

create a loop, that will fill 300 cells, from 300 checkboxes

I want to write a value (1) into a desired cell within Excel 2007, when I select a checkbox. The checkbox is in a Visual Basic userform, not on the active sheet itself.
The value (1) must revert back to zero when the checkbox is not selected.
I managed to get it working, however, I have more than 300 check-boxes, and want to know how to create one code that will do it in a loop?
{
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ThisWorkbook.Sheets("sheet3").Range("b8").Value = 1
Else: ThisWorkbook.Sheets("sheet3").Range("b8").Value = 0
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
ThisWorkbook.Sheets("sheet3").Range("b9").Value = 1
Else: ThisWorkbook.Sheets("sheet3").Range("b9").Value = 0
End If
End Sub
Private Sub CheckBox3_Click()
If CheckBox3.Value = True Then
ThisWorkbook.Sheets("sheet3").Range("b10").Value = 1
Else: ThisWorkbook.Sheets("sheet3").Range("b10").Value = 0
End If
End Sub
}
from checkbox 1 to checkbox 300, the cell range will be "B8" all the
way to "B308"
checkbox1 = cell range b8
checkbox2 = cell range b9
checkbox3 = cell range b10
checkbox4 = cell range b11
etc.......
Use IIf to set a value based on True or False.
Private Sub CheckBox1_Click()
ThisWorkbook.Sheets("sheet3").Range("B8").Value = IIf(CheckBox1.Value, 1, 0)
End Sub
Private Sub CheckBox2_Click()
ThisWorkbook.Sheets("sheet3").Range("B9").Value = IIf(CheckBox2.Value, 1, 0)
End Sub
Private Sub CheckBox3_Click()
ThisWorkbook.Sheets("sheet3").Range("B10").Value = IIf(CheckBox3.Value, 1, 0)
End Sub
I don't know how your workbook/userform looks in detail, but this should show pretty good how to do it:
First, we need the class which we will call Class1. In this class we put the code:
Option Explicit
Public WithEvents CBoxC As MSForms.CheckBox
Private Sub CBoxC_Change()
Dim i As Long
i = CLng(Replace(CBoxC.Name, "CheckBox", ""))
Sheet3.Cells(i + 7, 2).Value = 0 - CBoxC.Value
End Sub
Now we need a variable which "transfere" the events to our class. We jast add to any module:
Option Explicit
Public CBox() As New Class1
As the last step, we need to insert all the controls into our variable. So we add (or just include, if already there):
Option Explicit
Private Sub UserForm_Initialize()
Dim b As Variant
For Each b In Me.Controls
If TypeName(b) = "CheckBox" Then
If (0 / 1) + (Not Not CBox) = 0 Then ReDim CBox(0) Else ReDim Preserve CBox(UBound(CBox) + 1)
Set CBox(UBound(CBox)).CBoxC = b
End If
Next
End Sub
Instead of _Click we better use _Change. This way also keyboard-input will work...
As it is pretty much no code, it also should be self explaining. Just keep in mind, that such events will come last. (which should not matter in your case)
If you still have any questions, just ask ;)

Userform closes after “End Sub” without ever calling “Unload Me”

I have a userform (baseUF) that has multiple pages and buttons that all do different things. I have this baseUF being modeless because I want the user to be able to play with the sheet without closing the userform and losing all of the data they input. However, I started having a problem that might be due to the modeless nature of the baseUF.
There are other userforms that can be called from the baseUF. One executes with no issue by double clicking a textbox. However, the other userform is loaded after a button click. Once that button click sub is finished, the baseUF closes after the Exit Sub OR End Sub line. I don't remember this happening in the past and it doesn't happen with any other button click subs.
Does anybody have an idea what the issue could be? I'm pretty lost because I don't have a command to close the baseUF anywhere in that sub. Below is some code to show what is happening:
This sub is connected to a button on the spreadsheet to open the baseUF (the code is in a module).
Sub Button1_Click()
' show the userform
baseUF.Show vbModeless
End Sub
And this is the sub in the baseUF that calls an additional userform (LoadBox) which seems to be the issue.
Private Sub LoadQuery_Click()
' I Dim a bunch of stuff here
' if there are no saved queries, alert the user
If saveSht.Range("B3").Value = "" Then
MsgBox "No saved queries!"
Exit Sub
' if there is only one saved query, add it to the array and pop up the userform that allows for the user to select which save to load
ElseIf saveSht.Range("B4").Value = "" Then
save_names = saveSht.Range("B3").Value
LoadBox.Show
' otherwise, add all of the save names to the array and pop up that userform
Else
save_names = saveSht.Range(saveSht.Range("B3"),saveSht.Range("B3").End(xlDown)).Value
LoadBox.Show
End If
' if the user didn't select a save to load, stop trying to make stuff happen
If load_name = "" Then
' the userform will also close here if this turns out to be true
Exit Sub
End If
' do a bunch of stuff with the selected name here
' and after this line, the userform that contains this code closes
End Sub
EDIT: here is some code showing the two other userforms
This one is the userform with no issue that is called after a textbox is double clicked
Private Sub UserForm_Initialize()
' On start up of this form, populate the listbox with the relevant column names
' Set position
Me.StartUpPosition = 0
Me.Top = baseUF.Top + 0.5 * baseUF.Height - 0.5 * Me.Height
Me.Left = baseUF.Left + 0.5 * baseUF.Width - 0.5 * Me.Width
With FilterSelectionBox
' First grab all of the column names from the main selected table
For i = 0 To baseUF.SelectionBox.ListCount - 1
.AddItem baseUF.SelectionBox.List(i)
Next i
' Then grab all of the column names from the additional tables to be joined
If Not IsVariantEmpty(join_table_cols) Then
For n = 0 To UBound(join_table_cols)
If Not IsEmpty(join_table_cols(n)) Then
For Each col_name In join_table_cols(n)
.AddItem col_name
Next
End If
Next n
End If
End With
End Sub
Private Sub OkButton_Click()
' Initialize the variables
Dim tb As MSForms.TextBox
Dim arr() As String
Dim str As String
' tb is the textbox object that the column names will be pasted in to
Set tb = baseUF.MultiPage1.Pages(baseUF.MultiPage1.Value).Controls(Me.Tag)
' sets the str according to some logic
' This is actually where it gets sent
tb.Value = str
' And close the form
Unload Me
End Sub
And this is the code in the userform with an issue
Private Sub UserForm_Initialize()
' On initialization, populate the combobox with all of the save names present in the spreadsheet
' Set position
Me.StartUpPosition = 0
Me.Top = baseUF.Top + 0.5 * baseUF.Height - 0.5 * Me.Height
Me.Left = baseUF.Left + 0.5 * baseUF.Width - 0.5 * Me.Width
With LoadComb
' If there is more than one save present, go through the array and add each one
If IsArray(save_names) Then
For Each saved_name In save_names
.AddItem saved_name
Next
' Otherwise just add the one
Else
.AddItem save_names
End If
End With
End Sub
Private Sub LoadButton_Click()
' When the user hits the load button, first check if they actually selected anything
If LoadComb.Value = "" Then
' If they didn't, yell at them
MsgBox "No saved query selected!"
Else
' Otherwise, save the name to a global variable
load_name = LoadComb.Value
End If
' Close the form
Unload Me
End Sub
Whenever something unexpected happens with forms, consider writing End in the immediate window and pressing enter. It will kill all the unkilled instances of a form and generally any variable, thus it would be like a cold restart to the VBA program.
After doing this, it is a good idea to consider a cleaner solution, concerning VBA & UserForms, using some OOP. (Disclaimer - the first article is mine):
http://www.vitoshacademy.com/vba-the-perfect-userform-in-vba/
https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/
https://codereview.stackexchange.com/questions/154401/handling-dialog-closure-in-a-vba-user-form
Although it may seem that you are achieving the same results with more code, the benefits of using this approach are quite a lot in the long term.
This is a small example of the OOP model. Imagine you have a user form like this:
It has only the following controls:
btnRun
btnExit
lblInfo
frmMain (the class)
The code withing the form is the following:
Option Explicit
Public Event OnRunReport()
Public Event OnExit()
Public Property Get InformationText() As String
InformationText = lblInfo.Caption
End Property
Public Property Let InformationText(ByVal value As String)
lblInfo.Caption = value
End Property
Public Property Get InformationCaption() As String
InformationCaption = Caption
End Property
Public Property Let InformationCaption(ByVal value As String)
Caption = value
End Property
Private Sub btnRun_Click()
RaiseEvent OnRunReport
End Sub
Private Sub btnExit_Click()
RaiseEvent OnExit
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
Hide
End If
End Sub
The form is with two events, getting caught by the clsSummaryPresenter. The clsSummaryPresenter looks like this:
Option Explicit
Private WithEvents objSummaryForm As frmMain
Private Sub Class_Initialize()
Set objSummaryForm = New frmMain
End Sub
Private Sub Class_Terminate()
Set objSummaryForm = Nothing
End Sub
Public Sub Show()
If Not objSummaryForm.Visible Then
objSummaryForm.Show vbModeless
Call ChangeLabelAndCaption("Press Run to Start", "Starting")
End If
With objSummaryForm
.Top = CLng((Application.Height / 2 + Application.Top) - .Height / 2)
.Left = CLng((Application.Width / 2 + Application.Left) - .Width / 2)
End With
End Sub
Private Sub Hide()
If objSummaryForm.Visible Then objSummaryForm.Hide
End Sub
Public Sub ChangeLabelAndCaption(strLabelInfo As String, strCaption As String)
objSummaryForm.InformationText = strLabelInfo
objSummaryForm.InformationCaption = strCaption
objSummaryForm.Repaint
End Sub
Private Sub objSummaryForm_OnRunReport()
MainGenerateReport
Refresh
End Sub
Private Sub objSummaryForm_OnExit()
Hide
End Sub
Public Sub Refresh()
With objSummaryForm
.lblInfo = "Ready"
.Caption = "Task performed"
End With
End Sub
Finally, we have the modMain, which is the so-called business logic of the form:
Option Explicit
Private objPresenter As clsSummaryPresenter
Public Sub MainGenerateReport()
objPresenter.ChangeLabelAndCaption "Starting and running...", "Running..."
GenerateNumbers
End Sub
Public Sub GenerateNumbers()
Dim lngLong As Long
Dim lngLong2 As Long
tblMain.Cells.Clear
For lngLong = 1 To 10
For lngLong2 = 1 To 10
tblMain.Cells(lngLong, lngLong2) = lngLong * lngLong2
Next lngLong2
Next lngLong
End Sub
Public Sub ShowMainForm()
If (objPresenter Is Nothing) Then
Set objPresenter = New clsSummaryPresenter
End If
objPresenter.Show
End Sub

WithEvent in Class module not getting variable from Let Property in Excel VBA

I think I'm missing something simple here. Trying to use a variable in a Class module. Let and Get work fine. But if I try to use the variable in a different sub in the Class module I just get a value of 0.
Class Module clsCombobox
Public WithEvents ComboBox As MSForms.ComboBox
Public WithEvents ComboTextBox As MSForms.TextBox
Public Num As Long
Public Property Let Number(Value As Long)
Num = Value
End Property
Public Property Get Number() As Long
Number = Num
End Property
Private Sub ComboBox_Change()
Me.ComboTextBox.Value = Num
'this gives value of 0
End Sub
Userform Module
Dim obEvents as clsCombobox
Set obEvents = New clsCombobox
obEvents.Number = 52
MsgBox obEvents.Number 'this prints 52
Sub that sets ComboBox
Private Sub GroupCombobox()
Dim i As Long
Dim obEvents As clsCombobox
Set collCombobox = New Collection
For i = 1 To 5
Set obEvents = New clsCombobox
Set obEvents.ComboBox = Me.Controls("cbAbility" & i)
Set obEvents.ComboTextBox = Me.Controls("tbAbility" & i & "Text")
collCombobox.Add obEvents
Next i
End Sub
It doesn't look like you ever set the value. I assume you mean to do that in the loop? Perhaps not with the value of i, but here you can see the idea...
For i = 1 To 5
Set obEvents = New clsCombobox
'Set the value here
obEvents.Number = 52 ' 52 or whatever is needed as Number
Set obEvents.ComboBox = Me.Controls("cbAbility" & i)
Set obEvents.ComboTextBox = Me.Controls("tbAbility" & i & "Text")
collCombobox.Add obEvents
Next i

How to use a Class with a Collection of Controls

I tried adapting the solution in the link below to make a collection of text boxes allow numbers only. I get no error but the class just doesn't apply to the textboxes.
Excel VBA Userform - Execute Sub when something changes
Class Module
Public WithEvents TextGroup As MSForms.TextBox
Public Property Set Control(tb As MSForms.TextBox)
Set TextGroup = tb
End Property
Private Sub TextGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub
UserForm
Dim tbCollection As Collection
Private Sub UserForm_Initialize()
Dim obj As clsTextBox
Dim ctrl As Control
Set tbCollection = New Collection
tbCollection.Add Me.tbAC
tbCollection.Add Me.tbCR
tbCollection.Add Me.tbHP
For Each ctrl In tbCollection
Set obj = New clsTextBox
Set obj.Control = ctrl
Next
End Sub
You need to put the obj objects in the collection, not the controls themselves
Untested:
Dim tbCollection As Collection
Private Sub UserForm_Initialize()
Dim obj As clsTextBox
Dim arr
Dim ctrl
Set tbCollection = New Collection
arr = Array(Me.tbAC, Me.tbCR, Me.tbHP) '<< edit: no Set
For Each ctrl in arr
Set obj = New clsTextBox
Set obj.Control = ctrl
tbCollection.Add obj
Next
End Sub

VBA UserForm: SpinButtons not working when TextBoxes are formatted using Class Module

I have a bunch of spinbuttons that control the values in textboxes. I'm using a Class to allow all of them to use the same change event. It works great with unformatted boxes.
However when I try to make the textboxes show + and - numbers it doesn't work correctly. Only the first spin button will work. It's value needs to be set to a negative number before the next spinbutton will work. No buttons (other than the first) will work correctly unless the textbox directly above it in order is set to a negative number.
I've tried using .Text instead of .Value but it doesn't make a difference.
Class Module:
Public WithEvents SpinBtn As MSForms.SpinButton
Public WithEvents StatBox As MSForms.TextBox
Private Sub StatBox_Change()
Call UserForm1.ChangeSpin
End Sub
Private Sub SpinBtn_Change()
Call UserForm1.ChangeStat
End Sub
UserForm Module:
Dim collSpin As New Collection
Dim collStat As New Collection
Public Sub ChangeStat()
Dim i As Long
For i = 1 To 4
Me.Controls("StatBox" & i).Value = Me.Controls("SpinButton" & i).Value
Next
End Sub
Public Sub ChangeSpin()
Dim i As Long
For i = 1 To 4
Me.Controls("SpinButton" & i).Value = Me.Controls("StatBox" & i).Value
Me.Controls("StatBox" & i) = Format(Me.Controls("StatBox" & i), "+#;-#;+0")
'This is the line that breaks things
Next
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
Dim ctl As MSForms.Control
Dim obEvents As clsSpin
Call ChangeSpin
'Collect SpinButtons
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.SpinButton Then
For i = 1 To 4
If ctl.Name = "SpinButton" & i Then
Set obEvents = New clsSpin
Set obEvents.SpinBtn = ctl
collSpin.Add obEvents
End If
Next
End If
Next ctl
'Collect StatBoxes
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
For i = 1 To 4
If ctl.Name = "StatBox" & i Then
Set obEvents = New clsSpin
Set obEvents.StatBox = ctl
collStat.Add obEvents
End If
Next
End If
Next ctl
End Sub
Edit:
Thanks to #YowE3K for showing me a much easier and cleaner way!
Class:
Public WithEvents SpinBtn As MSForms.SpinButton
Public WithEvents StatBox As MSForms.TextBox
Private Sub StatBox_Change()
'prevents error when enter + or -
If IsNumeric(Me.StatBox.Value) = False Then
Else
'defaults to max or min of spinbutton when out of range
Select Case Me.StatBox.Value
Case Is < SpinBtn.Min
Me.SpinBtn.Value = Me.SpinBtn.Min
Case Is > SpinBtn.Max
Me.SpinBtn.Value = Me.SpinBtn.Max
Case Else
Me.SpinBtn.Value = Me.StatBox.Value
End Select
Me.StatBox.Value = Format(Me.StatBox.Value, "+#;-#;+0")
End If
End Sub
Private Sub SpinBtn_Change()
Me.StatBox.Value = Format(Me.SpinBtn.Value, "+#;-#;+0")
End Sub
Private Sub StatBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 43 '+
Case 45 '-
Case 48 To 57 '0-9
Case Else
KeyAscii = 0
End Select
End Sub
Userform:
Dim collSpinStat As New Collection
Private Sub UserForm_Initialize()
Dim i As Long
Dim obEvents As clsSpin
For i = 1 To 4
Set obEvents = New clsSpin
Set obEvents.SpinBtn = Me.Controls("SpinButton" & i)
Set obEvents.StatBox = Me.Controls("StatBox" & i)
Me.Controls("StatBox" & i) = Format(Me.Controls("StatBox" & i), "+#;-#;+0")
collSpinStat.Add obEvents
Next
End Sub
It seems like you are trying to link the "StatBox" controls with the "SpinButton" controls. If so, try the following:
Class "clsSpin":
Public WithEvents SpinBtn As MSForms.SpinButton
Public WithEvents StatBox As MSForms.TextBox
Private Sub StatBox_Change()
Me.SpinBtn.Value = Me.StatBox.Value
End Sub
Private Sub SpinBtn_Change()
Me.StatBox.Value = Format(Me.SpinBtn.Value, "+#;-#;+0")
End Sub
UserForm module:
Dim collSpinStat As New Collection
Private Sub UserForm_Initialize()
Dim i As Long
Dim obEvents As clsSpin
For i = 1 To 4
Set obEvents = New clsSpin
Set obEvents.SpinBtn = Me.Controls("SpinButton" & i)
Set obEvents.StatBox = Me.Controls("StatBox" & i)
collSpinStat.Add obEvents
Next
End Sub
FWIW, I would suggest using Labels instead of TextBoxes. Using TextBoxes means you will need to incorporate code into the StatBox_Change event to test whether the value entered by the user in the TextBox is actually valid or not. Using Labels means the user has to use the SpinButton to make the change.

Resources