Waiting for Input

**magness** · 01-13-2010, 01:14 PM

On the SetNum sheet is an ordered list of data sets. (the first set is set 0) The data itself is stored on a different sheet (Data). I would like the user to click the ModifySet button, enter a number, and have the form pop up with the previously entered data.

The SetNum control is an invisible label used for tracking the current set number. Its default value is 0.

The code for the button:

Private Sub ModifySet_Click()

SetForm.SetNum.Caption = InputBox("Please Enter Set Number", "Count Request Wizard")

If SetForm.SetNum.Caption > 0 And SetForm.SetNum.Caption <= WorksheetFunction.Max(Worksheets("SetNum").Range("B:B")) Then
    Debug.Print "Test " & SetForm.SetNum.Caption
    SetForm.Show
Else
    MsgBox ("Sorry, set number not valid.")
End If

End Sub

In theory, this is what should be pulling of the previous data entered. All of the controls have a corresponding named column in "Data". On Error is included since not all controls (such as labels) need/have their own column.

Private Sub UserForm_Initialize()

On Error Resume Next

Debug.Print "Int " & SetForm.SetNum.Caption

For Each Control In SetForm.Controls
    Name = Control.Name
    Control.Value = Worksheets("Data").Cells(SetForm.SetNum.Caption + 2, Worksheets("Data").Range(Name).Column).Value
Next Control

End Sub

Here is what I get from the two Debug.Print commands.

Int 0
Test 1

What I get is the UserForm coming up after I've input the SetNum with a SetNum.Caption of the correct value, but the data is always from Set0 (the default value for SetNum.Caption)

Am I just overlooking something?

Thanks,
Magness

**Leith Ross** · 01-13-2010, 02:00 PM

Hello Magness,

You are attempting to assign values to the UserForm before the UserForm has been loaded into memory. The Show command loads the UserForm into memory and then displays it. The Load command will place the UserForm into memory but not display the form. This allows for the properties and variables on the sheet to be set before displaying the form.

Private Sub ModifySet_Click()

Load SetForm

SetForm.SetNum.Caption = InputBox("Please Enter Set Number", "Count Request Wizard")

If SetForm.SetNum.Caption > 0 And SetForm.SetNum.Caption <= WorksheetFunction.Max(Worksheets("SetNum").Range("B:B")) Then
    Debug.Print "Test " & SetForm.SetNum.Caption
    SetForm.Show
Else
    MsgBox ("Sorry, set number not valid.")
End If

End Sub

**magness** · 01-13-2010, 03:03 PM

I had tried this (and did so again just now), but get the same results.

My button for adding a new set has this:

Private Sub AddSet_Click()

SetForm.SetNum.Caption = Cells(Worksheets("SetNum").Cells.SpecialCells(xlCellTypeLastCell).Row, 2) + 1
SetForm.Show

End Sub

And the set number is passing to label caption without Load SetForm here.

Would Load SetForm let me refer to controls without adding the SetFrom. to the front?

**Leith Ross** · 01-13-2010, 03:43 PM

Hello Magness,

You should post your workbook for review.

**magness** · 01-13-2010, 03:59 PM

Here you go. Thanks for taking a look at it. I apologize in advance for my naming conventions (or lack there of).

**Leith Ross** · 01-13-2010, 06:42 PM

Hello Magness,

I moved the variable "SetNum" from the General Declarations section of the UserForm to the top of Module1. This way it is visible to all parts of the project code as long as VBA is running.

Option Explicit

Public SetNum As Integer

Sub FormTtest()
    SetForm.Show
End Sub

Sub NameColumns()

Dim Name As String
Dim X As Integer
Dim N As Name

For Each N In Names
    N.Delete
Next

For X = 1 To 256
    If Cells(1, X).Value <> "" Then
        Name = Cells(1, X).Value
        Debug.Print Name
        Range(Cells(1, X), Cells(65536, X)).Name = Name
    End If
Next X

End Sub