Link input cells on user form to macro

[SteveDB1]

Hi all.
I have a macro that I want to use a user form with.
I've used the VBA editor to-- drag/drop-- build a form, and I need to link
the input text boxes to the functions of the macro so that it will make the
macro work the way that I want.
My questions are:
How do I need to do that?
According to the WROX VBA Excel 2007 programmers' reference book- chapter
13, I was to rename the text boxes to something that would make them
recognizable.
So, does that mean that my input boxes need to be named identical to the
macro's variable names?
I.e., if my macro variables are named: iRowV, iColFm, iColTo, iColMerge, am
I to name the text boxes those names?
Thanks for your help.

 

[OssieMac]

Hi Steve,

The book means to use a name that means something to you like GivenName,
SurName, Address etc. in lieu of TextBox1, TextBox2 etc.

When you right click on the text box in the design mode it will take you
into the VBA code area for them and automatically name a sub that will become
the event when you change the value or text in the text box.

Note that you have drop down boxes at the top of the VBA editor where you
can select the text box in the left drop down and in the right drop down you
select what type of action for which you want an event to take place.
Selecting these will automatically create the sub name and end sub between
which you enter the required code. Example: Select the text box by name in
the left drop down and then Exit in the right dropdown. The code for this
takes place when you have finished in the text box and exit out of it. In the
change event, the code takes place as you change it.

The following are examples of code to copy the data from the text boxes to
cells on the worksheet. Note that both .Text and .Value appear to work the
same. Someone might be able to provide more information on whether there are
any subtle differences but I have never found any.

Private Sub TextBox1_Change()
Sheets("Sheet1").Range("A10") = UserForm1.TextBox1.Text

'Alternative code using Value in liew of Text
'Sheets("Sheet1").Range("A10") = UserForm1.TextBox1.Value
End Sub


If you rename the text box from TextBox1 to MyTextBox, it would look like
this:-

Private Sub MyTextBox_Change()
Sheets("Sheet1").Range("A10") = UserForm1.MyTextBox.Text

'Alternative code using Value in liew of Text
'Sheets("Sheet1").Range("A10") = UserForm1.MyTextBox.Value
End Sub


Hope it helps and feel free to get back to me if you have any further
questions.

 

[SteveDB1]

Ossie,
Thank you for your help.
While I believe that I got the text boxes linked to my macro, I have another
item that's not working now.
The code for this component (and my understanding of it's task) is below:

iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row

iRowZ is declared as a range.
iColMerge is a variant.
my understanding-- and please tell me if I'm wrong, and correct me-- of the
main component here is
iRowZ = Cells(Rows.count, iColMerge).End(xlUp).Row
It counts the rows in the column of choice through to the end-- row by row.
The error that I'm getting is the 1004 error.
Last night when I first found it it said it was a global failure.
Now it's calling it a "Application-defined or object-defined error"
When the line of code is highlighted yellow,
iRowZ = 0, Rows.count = 1048576, iColMerge = 3, and .End(xlUp) = -4162
First, I would've thought that iRowZ would've started below the row that I
chose for iRowV.
Next, my goal of the macro was to have it only run to where the filled rows
stopped.
Yet Rows.count goes all of the way down to the very end of the worksheet.
Then, if I understand .End(xlUp) correctly, it backs up from the end of
Rows.count the amount specified.
Again, thank you.

 

[OssieMac]

Hi Steve,

Your code was trying to assign a row number to a range variable and it was
missing 'Set' which is required with objects assigned to variables. Study the
following and see what you can make of it.

Feel free to get back to me if you still have any questions.


Dim Irowz As Range
Dim iColMerge As Long
Dim numbRow As Long

iColMerge = 1 'Set to column A for testing

'Following assigns (Sets) range to a variable
'Note 'Set' is required at start of line.
'It will be the last non blank cell in column A
Set Irowz = Cells(Rows.Count, iColMerge).End(xlUp)

'Better (preferred) method. Note dot in front of cells and rows.count
With Sheets("Sheet1")
Set Irowz = .Cells(.Rows.Count, iColMerge).End(xlUp)
End With


'Following assigns entire row to a variable where
'it finds the last non blank cell in column A
Set Irowz = Cells(Rows.Count, iColMerge).End(xlUp).EntireRow

'Following assigns entire range to variable from A1
'to end of data in column A
'Note: Space and underscore is a line break in what
'is otherwise a single line of code
With Sheets("Sheet1")
Set Irowz = .Range(.Cells(1, iColMerge), _
.Cells(.Rows.Count, iColMerge).End(xlUp))
End With


'Following assigns row number to a variable
'It will be the row number of last non blank cell in column A
'Note: 'Set' not used here because it is not an object;
'simply a number
numbRow = Cells(Rows.Count, iColMerge).End(xlUp).Row

 

[SteveDB1]

Hi Ossie,
OK, from what I understand, you're declaring iRowZ as a range-- which I
already had-- and just didn't show.

You're then declaring iColMerge, and a new statement-- numbrow-- as Long.
I see where you placed the dots in front of Cells, and Rows.
Why Long?
My purposes will never require more than a double digit value to be input.


Let me ask:
Does the Sheets() require a sheet name in the parenthesis? I.e.,
Sheets(NameA)
I have to ask because the form/macro will be to access an identical
worksheet on all of our workbooks, but the sheet name will vary from workbook
to workbook.
And yes, we have renamed all of our worksheets within the workbook group/set
that my section uses, so there are no more workbooks with the name- Sheet_n,
etc..., being the default worksheet name.

Then, I see where you did
Set iRowZ = .cells(Rows.Count,iColMerge).End(xlUp)
However, I noticed that you removed .Row at the end of the equation line.
And I understood the underscore's purpose of a line break.

I'd tried what you'd written on Saturday, and still got back another error
type. I think it had to do with compiling.
After I got to work this morning, I realized that I'd never emailed myself
here what I'd done on Saturday-- at home.
I did however have a form basically made up, and repeated what I'd done up
to this point.
I'm now at the point where I was on Saturday.
I get the same errors, and all seems to be going as it did then.
I've tried your examples, and they are not working.
At this point it seems wisest to just post the entire macro-- form and all.
It'll be the next post-- to keep this one from being ridiculously long.
Thank you.

 

[SteveDB1]

Ok, the complete macro, including user form portion.
Thank you.
-----------------------------------------------
'This function will sum values of merged cells, without having to go
'through each line of data for summing manually.
'And yes, it works now, as written.
Sub IF_Merged_Sum(Optional control As IRibbonControl)
' the (Optional control as iRibbonControl) will ultimately allow it to be
accessed through a ribbon menu I've made.
'> I only want it looking at the merged rows of column C (owners'
'names). Can be changed from column C to more distant columns by changing
'numeric value.
Dim iColMerge As Variant
TxtCol1 = iColMerge
'TxtCol1 is user input box and supposed to assign value input by user to
iColMerge.
'> values located in column D to be summed. Can also be changed to
'column E, or F by changing from 4 to 5, or 6.
Dim iColFm As Variant
TxtCol2 = iColFm
'TxtCol2 is user input box and is supposed to assign value input by user to
iColFm
'> I want the sum valued results in column G. Can be changed from G to
'H, I, etc... by changing numeric value.
Dim iColTo As Variant
TxtCol3 = iColTo
'TxtCol3 is user input box and is supposed to assign value input by user to
iColTo.
Dim zCell As Range, iRowZ&, iRowN&, iRowV&,
' to find last used cell in column. Will stop at last used row.
'With Sheets("Sum")
'Set
'iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row
Dim NumRow As Long
NumRow = Cells(Rows.Count, iColMerge).End(xlUp).Row
'End With

' Dim iRowV As Variant- already declared as Range.
TxtRow1 = iRowV
'TxtRow1 is suppose to assign user input value to iRowV.
'set this number at starting row of ownership values. If
'further down than 4th row, set to that value, i.e., 9th, 10th, etc....
Do While iRowV <= iRowZ
Set zCell = Cells(iRowV, iColMerge)
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count <> 1 Then Stop ' Found error, and
'stops.
iRowN = iRowV + zCell.MergeArea.Rows.Count - 1
Cells(iRowV, iColTo).Formula = "=sum(" & _
Cells(iRowV, iColFm).Address & _
":" & _
Cells(iRowN, iColFm).Address & _
")"
iRowV = iRowN + 1
Else
iRowV = iRowV + 1
End If
Loop
End Sub


Private Sub TxtCol1_Change()

End Sub

Private Sub TxtCol2_Change()

End Sub

Private Sub TxtCol3_Change()

End Sub

Private Sub TxtRow1_Change()

End Sub

Private Sub UserForm_Click()

End Sub
Option Explicit
Public Cancelled As Boolean

Private Sub CmdCancel_Click()
Cancelled = True
Me.Hide
End Sub

Private Sub CmdOK_Click()
Call IF_Merged_Sum
Cancelled = False
Me.Hide
End Sub

-------------------------------------------------

 

[OssieMac]

Hi Steve,

I’m sorry but without the data which is being manipulated, I am not able to
follow your code sufficiently to identify all the problems with it. However,
to answer some of your other questions.

The sample code that I gave you was only that. Sample code. It was only
meant to show you how to assign ranges and rows to variables because it
appeared to me that you were trying to assign a row number to a range
variable. I was trying to demonstrate that you can’t assign a row number to a
variable dimensioned as a range and how you apply that information in your
code is up to you.

The reason for dimensioning numRow as long is because it must be numeric and
if dimensioned as an integer then it will only accommodate rows up to 32,767.
An interger is fine if you are certain that your code will not require a
larger number.

I have included some more code samples which might help you to decide how to
dimension a variable. You can leave the variable to default to a variant and
the sample code shows how to find out what type of variable VBA thinks it
should be.

With the Sheet names, it is up to you how you handle them in your
application. I like to be specific and use the Code names. However, I have
included some more sample code options on how you can address worksheets.

Code sample:-

Sub Demo_code_2()
'Finding what to dimension a variable as.
'Copy this code into module in a blank workbook.
'Initially let the variables default to Variant
Dim iRowZ
Dim iColMerge
Dim numbRow

'Note: The msgbox will show the following as long
'because it is greater than the integer range.
iColMerge = 33000
MsgBox "iColMerge is " & TypeName(iColMerge)

'Note: The msgbox will show the following as integer
'because it is less than the maximum integer range.
iColMerge = 32000
MsgBox "iColMerge is " & TypeName(iColMerge)


With Sheets("Sheet1")
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With
MsgBox "Irowz is " & TypeName(iRowZ)

End Sub



Sub Demo_code_3()
'Alternative methods of addressing worksheets.

Dim ws1 As Worksheet
Dim numbRow As Long
Dim iRowZ As Range

'Assign the active sheet to a variable
Set ws1 = ActiveSheet

'Use the variable in lieu of the worksheet name
With ws1
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With

With ws1
'Note: Set is not used to return a row number
numbRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


'Using the code name of the worksheet. See the code name
'in the Project Explorer section of the VBA Editor.
'The code name is the one NOT in brackets. The name in
'brackets is the user defined name.
'The code name does not change when the user defined name
'is changed and is often a good reference to use in macros.

With Sheet1
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With

'Using the worksheet index to reference a worksheet.
'Index is counted from the left as the tabs are displayed.
'If the sheets are re-ordered then the index is re-ordered.
With Sheets(1)
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With

End Sub

 

[SteveDB1]

Hi Ossie,
I suppose I need to make this statement.
The macro worked fine when I had constants for the iColMerge, iColFm,
iColTo, and iRowV variables.
I.e.,
Const iColMerge = n_1
Const iColTo = n_2
Const iColFm = n_3
Const iRowV = n_4 (where n_x are the constant numeric values I'd initially
chosen)

I now want to make these four items variables to have a userform to input my
choices.

I made a basic user form, and then connected the input text boxes.
Now I get a compile error-- a 424 error-- stating that the formula that I'd
initially given
iRowZ = .Cells(.Rows.Count, 1).End(xlUp).row
is no good.

My iniital assumption (I know, assumptions are not smart) was that my input
boxes have been incorrectly done.

And I only say that because the macro worked, and when I use constants,
still works.

 

[OssieMac]

Hi again Steve,

If the macro worked before then without going into what the entire macro
does, perhaps the following might help you.

What are you trying to return into iRowZ ? Is it the row number or a range?
The following line returns a row number and iRowZ should be dimensioned as
long (or could use integer if under about 32,000):-

iRowZ = .Cells(.Rows.Count, 1).End(xlUp).row

However, to use the code with the dots in front of Cells and Rows.Count as
above, you need to have it nested in a With/End With statement like this:-

With Sheets("Sheet1")
iRowZ = .Cells(.Rows.Count, 1).End(xlUp).row
End With

If not nested and you are allowing it to default to the active sheet then it
should look like this:-

iRowZ = Cells(Rows.Count, 1).End(xlUp).row

I am fresh out of ideas so perhaps you should start a new thread and post
your macro as you had it working previously plus the macro you have now with
a description of where it is failing and perhaps someone else will be able to
see what is wrong with it. (Include the entire macros with Dim statements).