Referencing class variable(s) from another class

simurq

Board Regular
Joined
Nov 11, 2011
Messages
73
I have two simple classes shown below for several userform textbox and spin button controls. Userform_Initiliaze event basically adds any such controls to relevant collections to manipulate later using events. What I want to achieve is to pass text from textbox class (clsTextBox) to spin button class (clsSpinBtn) to increase/decrease the numeric value of the textbox input but I always get "Runtime Error 91: Object Variable or With Block variable not set" whenever I try to spin up or down.
CheckTextBoxValue() is just a function to control and convert any string input into a numeric (double) value.

Any help is highly appreacited!!!

UserForm_Initialize()

<font face=Calibri><SPAN style="color:#00007F">Public</SPAN> colTextBox <SPAN style="color:#00007F">As</SPAN> Collection<br><SPAN style="color:#00007F">Public</SPAN> colSpinBtn <SPAN style="color:#00007F">As</SPAN> Collection<br><br><SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>    <SPAN style="color:#00007F">Dim</SPAN> clsTextBox <SPAN style="color:#00007F">As</SPAN> clsTextBoxes<br>    <SPAN style="color:#00007F">Dim</SPAN> clsSpinBtn <SPAN style="color:#00007F">As</SPAN> clsSpinButtons<br>    <SPAN style="color:#00007F">Dim</SPAN> ctl <SPAN style="color:#00007F">As</SPAN> control<br><br>    <SPAN style="color:#00007F">Set</SPAN> colTextBox = <SPAN style="color:#00007F">New</SPAN> Collection<br>    <SPAN style="color:#00007F">Set</SPAN> colSpinBtn = <SPAN style="color:#00007F">New</SPAN> Collection<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ctl <SPAN style="color:#00007F">In</SPAN> Controls<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> ctl <SPAN style="color:#00007F">Is</SPAN> MSForms.TextBox <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> clsTextBox = <SPAN style="color:#00007F">New</SPAN> clsTextBoxes<br>            <SPAN style="color:#00007F">Set</SPAN> clsTextBox.InputTextBox = ctl<br>            colTextBox.Add clsTextBox<br>        <SPAN style="color:#00007F">ElseIf</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> ctl <SPAN style="color:#00007F">Is</SPAN> MSForms.SpinButton <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> clsSpinBtn = <SPAN style="color:#00007F">New</SPAN> clsSpinButtons<br>            <SPAN style="color:#00007F">Set</SPAN> clsSpinBtn.InputSpinButton = ctl<br>            colSpinBtn.Add clsSpinBtn<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> clsTextBox = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> clsSpinBtn = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>

CANCEL Button to free memory

<SPAN style="color:#00007F">Sub</SPAN> btnCancelColor_Click()<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> colTextBox = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> colSpinBtn = <SPAN style="color:#00007F">Nothing</SPAN><br>    Unload Me<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>

Class for TEXTBOXES

<SPAN style="color:#00007F">Public</SPAN> WithEvents InputTextBox <SPAN style="color:#00007F">As</SPAN> MSForms.TextBox<br><SPAN style="color:#00007F">Private</SPAN> mvarText <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#007F00">' Selects only the numeric part of textbox content</SPAN><br><SPAN style="color:#007F00">' Otherwise selects the whole string</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> InputTextBox_MouseDown(<SPAN style="color:#00007F">ByVal</SPAN> Button <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Shift <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> numPart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, numLength <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        <br>    <SPAN style="color:#00007F">With</SPAN> InputTextBox<br>        numLength = InStr(1, .Value, Space(1))<br>        numPart = Left(.Value, numLength)<br>        .SelStart = 0<br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(numPart) <SPAN style="color:#00007F">Then</SPAN><br>            .SelLength = numLength - 1<br>        <SPAN style="color:#00007F">Else</SPAN><br>            .SelLength = Len(.Value)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#007F00">' MouseUp event is a 'cosmetic' way to prevent changing textbox content</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> InputTextBox_MouseUp(<SPAN style="color:#00007F">ByVal</SPAN> Button <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Shift <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> numPart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, numLength <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> InputTextBox<br>        numLength = InStr(1, .Value, Space(1))<br>        numPart = Left(.Value, numLength)<br>        .SelStart = 0<br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(numPart) <SPAN style="color:#00007F">Then</SPAN><br>            .SelLength = numLength - 1<br>        <SPAN style="color:#00007F">Else</SPAN><br>            .SelLength = Len(.Value)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Property</SPAN> <SPAN style="color:#00007F">Let</SPAN> sText(<SPAN style="color:#00007F">ByVal</SPAN> sData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    mvarText = sData<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Property</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Property</SPAN> <SPAN style="color:#00007F">Get</SPAN> sText() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    sText = mvarText<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Property</SPAN><br><br><br>

Class for SPIN BUTTONS

<SPAN style="color:#00007F">Public</SPAN> WithEvents InputSpinButton <SPAN style="color:#00007F">As</SPAN> MSForms.SpinButton<br><br><SPAN style="color:#007F00">' Increases/Decreases textbox value depending on the size of its numeric part</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> InputSpinButton_SpinDown()<br>    <SPAN style="color:#00007F">Dim</SPAN> NumericValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> oTextBox <SPAN style="color:#00007F">As</SPAN> clsTextBoxes<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> oTextBox = <SPAN style="color:#00007F">New</SPAN> clsTextBoxes<br>    <br>    <SPAN style="color:#00007F">With</SPAN> oTextBox.InputTextBox<br>        <SPAN style="color:#00007F">If</SPAN> .sText = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>            NumericValue = 0<br>        <SPAN style="color:#00007F">Else</SPAN><br>            NumericValue = CheckTextBoxValue(.sText) - 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> NumericValue > 99 <SPAN style="color:#00007F">Then</SPAN> NumericValue = 100<br><br>    <SPAN style="color:#00007F">Set</SPAN> oTextBox = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> InputSpinButton_SpinUp()<br>    <SPAN style="color:#00007F">Dim</SPAN> NumericValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> oTextBox <SPAN style="color:#00007F">As</SPAN> clsTextBoxes<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> oTextBox = <SPAN style="color:#00007F">New</SPAN> clsTextBoxes<br>       <br>    <SPAN style="color:#00007F">With</SPAN> oTextBox.InputTextBox<br>        <SPAN style="color:#00007F">If</SPAN> .sText = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>            NumericValue = 0<br>        <SPAN style="color:#00007F">Else</SPAN><br>            NumericValue = CheckTextBoxValue(.sText) + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> NumericValue > 99 <SPAN style="color:#00007F">Then</SPAN> NumericValue = 100<br><br>    <SPAN style="color:#00007F">Set</SPAN> oTextBox = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,129,876
Messages
5,638,788
Members
417,052
Latest member
Noobest

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
Top