1 userform: txtbox data sent to cells in workbk on

Bafa · July 16th, 2008, 01:21 PM

All I know about VBA in Excel is what I have cannablized from other people's xls files.
So a lot of the commands throw me on how exactly I am supposed to implement them. I
just discovered macros in the modules about two days ago. So if you can help me, talk
to me like I am a child :P

this is what I have always used to put data from my userform into my spreadsheet:

Code:

 Private Sub TextBox1_Change()
       Range("A1").Value = Me.TextBox1.Value
End Sub

Now I need more advanced code. Here is what i have:
I have one userform i want to use for everything I need. It has a calendar that I
click to choose the date I will be working with. I have seven buttons that match 7
worksheets set up with exactly the same cell structure and labeling, the only difference
is the name of the worksheet is the catagory that data needs to be in, hence the 7
worksheets.

Here is what I need:
click the date I need the proper row to recieve information from the textboxes
boxes in my userform and the row it needs to go in should be assigned o the textboxes.
Each textbox should put data into it's assigned column no matter which sheet or row I am
on, but the column the data from the textbox goes into is decided by the date I clicked.
With a possible range of 1-31 for the days of the month.
Now when i click one of my seven commandbuttons I need the information to go to the
correct sheet.

So simplisticly I needthe code in my userform to take what is entered into it and input
that data into my spreadsheet with these settings:
CommandButton_Click goes to sheet needed
TextBox goes to the column needed
Date's day selected (1-31) goes to rown needed.

I think I have a solution to the first problem. Its pretty simplistic and isn't as nice

as it all happening behind the scenes.

Code:

Private Sub CommandButton1_Click()
Worksheets("Exams").Activate
End Sub

That will at least make the sheet I need the info to into active. I just make each of
the 7 buttons load the needed page.
I am just not learned enough yet to take it further than that solution. Of course I am
open to a more streamlined solution.

The second part of code I need help with really has me stumped. I have been reading a
lot of tutorials and I am
wondering if something like this would help:

Code:

Dim MyDate, MyDay
MyDate = Worksheets("Calc").Range("B1").Value
MyDay = Day(MyDate)

("Calc").Range("B1") is where my calander inserts the date when clicked. If this little
code breaks it down to a simple
number bewteen 1 and 31 then I just need to understand how to insert it into a macro
that targets the correct cell.
I have been using

Code:

 Private Sub TextBox1_Change()
       Range("A1").Value = Me.TextBox1.Value
End Sub

but to continue to do that would take 31 userforms. So I need to figure out how to
replace "A1" with something like

Code:

 Private Sub TextBox1_Change()
       Range("A.MyDay").Value = Me.TextBox1.Value
End Sub

but I have yet to find anything like that in examples I have been pouring
over. I know that isn't real code,
but its the only way I can think to explain it.
On a side note do I need to be doing something with the LabelRange Dialogue box on the
spreadsheet side of this to get what I want to work?

Thanks!

BrianWren · July 16th, 2008, 02:31 PM

First of all, I would think a better interface would be either a combo box or a set of 7 radio buttons to indictate the sheet to go to, and one ommand button.

With one "Do It" button, it seems things would be clearer.
The combobox method is shown below.

Code:

Sub DoIt_Click()

    Worksheets(Me.cboSheet.Value).Activate ' or .Text; I always forget.

    Range("A" & MyDay).Value = Me.TextBox1.Value

End Sub

("A" & MyDay) evaluates to a 2 or 3 character string of "A1" through "A31".

Does that do it for you?

Bafa · July 17th, 2008, 10:54 AM

Not do it, but helped a great deal.

Mkay, I have the selcting of the worksheet set up with the combo box like so:

Code:

Private Sub UserForm_Initialize()
         'this grabs the date from the calendar
     If IsDate(Worksheets("calc").Range("b1").Value) Then
            Calendar1.Value = DateValue(Worksheets("calc").Range("b1").Value)
      Else
            Calendar1.Value = Date
      End If

    ComboBox1.AddItem "Total Exams"                      'ListIndex = 0
    ComboBox1.AddItem "Overexposed Films"                'ListIndex = 1
    ComboBox1.AddItem "Underexposed Films"               'ListIndex = 2
    ComboBox1.AddItem "Films With Positioning Errors"    'ListIndex = 3
    ComboBox1.AddItem "Films With Motion"                'ListIndex = 4
    ComboBox1.AddItem "Films With Artifacts"             'ListIndex = 5
    ComboBox1.AddItem "Films With Miscellaneous Problem" 'ListIndex = 6

    'Use drop-down list
    ComboBox1.Style = fmStyleDropDownList
    'Combo box values are ListIndex values
    ComboBox1.BoundColumn = 0
    'Set combo box to first entry
    ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Click()

    Select Case ComboBox1.Value

    Case 0  'Exams
         'Sets the active worksheet for dat input
        Worksheets("Exams").Activate
    Case 1  'Over
         Worksheets("Over").Activate

    Case 2  'Under
                Worksheets("Under").Activate

    Case 3  'Position
                Worksheets("Position").Activate

    Case 4  'Motion
                Worksheets("Motion").Activate

    Case 5  'Artifact
           Worksheets("Artifact").Activate

    Case 6  'Miscellaneous
           Worksheets("Misc").Activate

     End Select

     'This loads data already entered on spreadsheet
     'back into the userform input boxes for user to track
     'what has been done so far
      Me.TextBox1.Value = Range("A1").Value
      Me.TextBox2.Value = Range("B1").Value
      Me.TextBox3.Value = Range("C1").Value
      Me.TextBox4.Value = Range("D1").Value
      ' etc....
End Sub

            'All the following TextBox_Change()
            'enters data you put in userform to spreadsheet
Private Sub TextBox1_Change()
        Range("A1").Value = Me.TextBox1.Value
End Sub
Private Sub TextBox2_Change()
        Range("B1").Value = Me.TextBox2.Value
End Sub
Private Sub TextBox3_Change()
        Range("C1").Value = Me.TextBox3.Value
     'etc....
End Sub

This all works great. (unless you see something that could be better) What i am having problems with is

Code:

Dim MyDate, MyDay
Dim i As Long
MyDate = Worksheets("Calc").Range("B1").Value
i = MyDay.Value
MyDay = Day(MyDate)

I am getting a runtime error 424 Object required.
The thing is I don't really "get" this MyDay thing. I am not really sure where to put it either. In the initialize
section of userform? In the ComboBox1_Click section? I am trying to get "i" to = MyDay so that when I write:

Code:

Range("E1").Value = Me.TextBox5.Value

I can actually write this instead:

Code:

Range("E" & MyDay).Value = Me.TextBox5.Value

So where MyDay is will actually be a number value somewhere between 1 and 31 that is equal to the day of the
month selected in the calendar. Which in turn will enter the data on the correct row.

Wow... so close to getting this done! :D

Thanks!

BrianWren · July 17th, 2008, 05:02 PM

When you do this:

Code:

    Dim ... MyDay
    ...
    i = MyDay.Value

MyDay is a Variant. (Untyped variables are made Variants.) Variants do not initially have properties that can ba accessed through a dot. If, on the other hand, you use Set MyDay = <someObjectOrOther>, where the object has a .Value property, then MyDay.Value will mean something because the requirement to be an object will have been met.

Also, in what you have shown, you set i to a value, but then never use it...
With

Code:

    Dim MyDate, MyDay
    MyDate = Worksheets("Calc").Range("B1").Value
    MyDay = Day(MyDate)

    ' you effectively have
    Dim MyDay
    MyDay = Day(Worksheets("Calc").Range("B1").Value)

Having done this, then you can use MyDay in a concatenation ("E" & MyDay)...

Bafa · July 17th, 2008, 07:12 PM

That worked! Thanks a bunch. This is great. heheh I found out that I needed to put that statement in every one of my textbox_change statements and it worked.

Now I am curious. Is there a way I can make :

Code:

    Dim MyDate, MyDay
    MyDate = Worksheets("Calc").Range("B1").Value
    MyDay = Day(MyDate)

Sit in a module named as a macro with something I made up like "Sub RowSelector()" and then call that macro to activate when I need it? like Instead of :

Code:

Private Sub TextBox5_Change()
    Dim MyDate, MyDay
    MyDate = Worksheets("Calc").Range("B1").Value
    MyDay = Day(MyDate)
        Range("E" & MyDay).Value = Me.TextBox5.Value
End Sub

I would like have:

Code:

Private Sub TextBox5_Change()
      Execute RowSelector      'I have no idea but just for example of what i need.
        Range("E" & MyDay).Value = Me.TextBox5.Value
End Sub

I am asking not only for this, but I have another set of instructions much larger that I need to be able to call in the same way.

BrianWren · July 18th, 2008, 12:57 PM

Code:

Private Function TheDay() As Integer
    TheDay = Day(Worksheets("Calc").Range("B1").Value)
End Sub

If you need to specify the "Calc" or "B1" parts, pass them in as arguments.

Code:

Private Function TheDay(SheetName As String, Loc As String) As Integer
    TheDay = Day(Worksheets(SheetName).Range(Loc).Value)
End Sub

Private Sub TextBox5_Change()
        Range("E" & TheDay()).Value = Me.TextBox5.Value
End Sub

or

Code:

Private Sub TextBox5_Change()
        Range("E" & TheDay("Calc", "B1")).Value = Me.TextBox5.Value
End Sub

You could put TheDay() in a separate module. If you do that, make it Public instead of Private.