How to send workbook sample and its vba code to this forum?

JOHNNYC · Jul 8, 2012

Hello, new guy here --to everything: forum, Excel, vba, you name it.

I'll be asking (begging) for help here and so have a need to know how to send workbook and proposed code to this forum so that people can get some idea what I'm talking about.

The current problem is VBA's stubborn insistence that a user form needs an object.
Still fuzzy on objects. Thought that userform module in project explorer WAS an object!

To this point, like the comedian going around the circuit stealing jokes, I've been going around the web stealing code and concepts then modifying to the routine's needs.

The routine is an attempt to create an extremely user friendly workbook for users who are unacquainted with excel or, for that matter, with the restrictions on which sorts of data are allowed.

If someone can help with how to send samples: is it just copy and paste?

john

Bob Phillips · Jul 8, 2012

A userform is an object, a class object.

To post a workbook, in a post, click the 'Go Advanced' button, then scroll down until you see a 'Manage Attachment' button, click that to upload a file.

JOHNNYC · Jul 9, 2012

Bob,
Thank you for your help.

WARNING: Its going to be obvious that I am NOT a programmer

This is the code I have so far:

Code:

Private Sub Workbook_Open()
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
startover:
    Dim Ans As Integer
    Ans = MsgBox("Would you like to enter an appointment?" _
    & vbNewLine & vbNewLine & "Click YES to continue step by step instructions" _
    & vbNewLine & vbNewLine & "Click NO to enter the appointment manually" _
    & vbNewLine & vbNewLine & "Click CANCEL to exit transport", vbYesNoCancel + vbQuestion, "ROOM NUMBER")
        Select Case Ans
            Case vbYes
            Columns("B:B").Select 'apt num col
            
GoTo getapt ' this line skips over the error code the first time through


etrap:
    MsgBox "Please enter a valid apartment number"
    GoTo getapt
        
        
        'get user to input apt num
        'apt num goes into the variable- answer
getapt:
        Dim answer As Variant
        answer = InputBox _
        ("Type the resident's 3 digit apartment number then press ok", _
        "APARTMENT NUMBER")
        If answer = "" Then GoTo startover 'user clicked <cancel>
        
        'weed out non-exsistent apt nums
        If answer <= 100 Then GoTo etrap
        If answer >= 273 Then GoTo etrap
        For x = 172 To 200
        If x = answer Then GoTo etrap
        Next
    
    'find entered apt num in apt num col and select its cell
    Selection.Find(What:=answer, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    
    
    'find last used cell on the row to the right
    ActiveCell.End(xlToRight).Select

    'move one cell to the right from the last used cell
    ActiveCell.Offset(0, 1).Select
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

    'get user appt date input
   
    Dim dInput As String
    Dim dMyDate As Date
    Dim DayNumber As Integer
    Dim dayname As String
    
Retry:
    dInput = InputBox("Enter the date in mm/dd/yy format: ")
        If IsDate(dInput) Then
        
        
        dMyDate = Format(dInput, "mm/dd/yyyy")
        Else
        MsgBox dInput & " is not a valid date format."
        GoTo Retry
        End If
         If dMyDate < Date Then

        MsgBox (dMyDate & " was entered." & vbNewLine & vbNewLine & "That date has past")
            GoTo Retry
        Else
            
        End If
         
         DayNumber = Weekday(dInput, vbSunday)
    
    
    Select Case DayNumber
        Case 1
            dayname = "Sunday"
        If dayname = "Sunday" Then GoTo wrongday
        
        Case 3
            dayname = "Tuesday"
        If dayname = "Tuesday" Then GoTo wrongday
       
        Case 5
            dayname = "Thursday"
        If dayname = "Thursday" Then GoTo wrongday
        
        Case 6
            dayname = "Friday"
            If dayname = "Friday" Then GoTo wrongday
        
        Case 7
            dayname = "Saturday"
            If dayname = "Saturday" Then GoTo wrongday
        

wrongday:
MsgBox ("The date entered falls on " & dayname & "." & vbNewLine & "Monday and Wednesday are the only valid medical appointment days.")
GoTo Retry
        
        Case 2
            dayname = "Monday"
        GoTo apptdate
        Case 4
            dayname = "Wednesday"
        GoTo apptdate
    End Select
    
apptdate:
        ActiveCell = dInput
        

'ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt
'Call Public Sub CommandButton1_Click() 'THIS LINE WAS NOT PART OF MY ORIGINAL ATTEMPT
    'appt time
    'fix this code to provide for error trapping
    ActiveCell.Offset(0, 1).Select
    Dim tInput As String
    Dim tMyTime As Date
    'WHENandWHEREUserForm.Show 'THIS LINE WAS ALSO NOT PART OF THE ORIGINAL ATTEMPT
tRetry:

    'tInput = InputBox("Enter the appointment time" & vbNewLine & "in hour : minute format: ")
     '   If IsDate(tInput) Then
        
        
      '  tMyTime = Format(tInput, "hh:mm")
       ' Else
        'MsgBox tInput & " is not a valid time format."
        'GoTo tRetry
        'End If
   
    
    
     
     
     
  
     
     
     'ActiveCell = tMyTime
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
     'Doc's name
     ActiveCell.Offset(0, 1).Select
     
     'town
     'disallow appt if time is wrong
     ActiveCell.Offset(0, 1).Select
     
     'get user input for address
     ActiveCell.Offset(0, 1).Select
            
            Case vbCancel
            Workbooks("transport.xlsm").Close SaveChanges:=True
            
            Case vbNo
        End Select

End Sub

In the code above, it's in trying to get an "error proof" time of day where the problem is..
The original attempt code (now commented out) actually worked but if I entered funny input I got funny output so a userform seemed a good solution (its the code between the line of ttttt..... and the line of nnnn.....)

The town listbox was included because the time and town must be compatible

The user form will not run It gets an OBJECT NEEDED error

The code below was copied and modified from the link (Not enough seniority to post links)

The instructions say to double click each control to see its code but that creates a new sub for each control. Is that the way it is supposed to work?

This is the code for userform:

Code:

Public Sub CommandButton1_Click()
WHENandWHEREUserForm.Show
End Sub



Private Sub CancelButton_Click()

Unload Me

End Sub


Private Sub ClearButton_Click()
Call UserForm_Initialize

End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Initialize()


'Empty CityListBox
TownBox.Clear

'Fill CityListBox
With TownBox
   .AddItem "Round Rock"
   .AddItem "Cedar Park"
   .AddItem "North Austin"
   .AddItem "Georgetown"
End With


'Empty HourBox
HourBox.Value = ""

'Empty HourBox
MinuteBox.Value = ""

'Set Focus on NameTextBox
HourBox.SetFocus
End Sub

Private Sub HourSpin_Change()


HourBox.text = HourSpin.Value

End Sub
Private Sub MinuteSpin_Change()
MinuteBox.text = MinuteSpin.Value
End Sub
Private Sub OKButton_Click()
ActiveCell.Value = TownBox.Value
End Sub

Aside from less sloppy structure does anyone have any suggestions for getting this code to work?

Bob Phillips · Jul 9, 2012

Johnny,

That code is confusing me somewhat. I can see code on workbook opening, but not sure what it is is trying to do (in overall business terms). As for the form, I cannot see where you show it. There is a commandbutton procedure invoking it, but that is within the form!

Can you give a few details.

BTW, I know those areas, North Austin, Georgetowm, Round Rock. They are lovely places.

Bob Phillips · Jul 9, 2012

BTW, you are not using Option Explicit in your code, and so VBA is not checking that you are using correct names. Your form calls the TextBox MinutetBox, and your code uses MinuteBox - not the same. You also use dInput and dtInput in the workbook open code.

JOHNNYC · Jul 10, 2012

Thank you for continued interest, Bob.

I will delete the extra "t."

This is a sheet that accepts reservations for transportation to medical appointments (doctors, xrays, labs, etc.). Those making the reservations are residents of senior home. There are 150 residents being serviced by 1 bus, so it is necessary to have certain restrictions (such as --medical appointments may only be made on Monday and Wednesday (the bus is used for other things the rest of the week) --appointments in Georgetown must be in the PM) This stuff is currently being recorded in a 3 ring binder by hand. Those who are taking the information from the residents and recording it sometime enter the info incorrectly or allow residents to schedule transport that cannot be honored because it violates the restrictions. this code attempts to allow people at the front desk to enter transportation to medical appointments in an excel sheet without going outside of parameters even if they know nothing about either the restrictions or excel. The front desk people are the users in this case.

Currently I'm having trouble with the code for entering the time of the appointment (that is, when the resident has to be at the doctor's office.)

If you were to remove this line

'Call Public Sub CommandButton1_Click() 'THIS LINE WAS NOT PART OF MY ORIGINAL ATTEMPT

and this line

'WHENandWHEREUserForm.Show 'THIS LINE WAS ALSO NOT PART OF THE ORIGINAL ATTEMPT

and then uncomment the rest of the time code, it would work. The user could enter a time, except if he made a mistake. Lets say a time was entered with a period instead of a semicolon as a separator. Then the time intended to be entered would not become the cell value.

A solution is to do it with a userform then concatenate the hour and minute with a "hard coded" semicolon. And while I'm at it might as well have the town and add code to check if town and time are compatible.

But I don't know how to get the userform to run. or where to put the code that gets it to run or even if the userform is correctly built.

Bob Phillips · Jul 10, 2012

I would suggest a userform is the best option here.

The userform does not have details like name, day, room etc., should these be added?

Shouls the input, when OKed, just go into the next appointment slot for that room?

JOHNNYC · Jul 10, 2012

The sheet's first three columns contain info that is more or less constant. It is updated only when an apartment is vacated or new residents move in.

Each row can be thought of as a record.

Starting with col D is where the reservation info is entered.

The code, so far, finds the proper resident's row after the user is prompted for an apartment number.

Then it finds the first empty cell in that row.

This is in col D if there have been no reservations previously made for that resident--otherwise it finds the first empty cell to the right.

There are 5 fields of info for each reservation. Appointment Date, Appointment Time, Doctor's or Facility"s Name, Town, Address.

[More to come, have to go to work]

JOHNNYC · Jul 10, 2012

JOHNNYC said:
The sheet's first three columns contain info that is more or less constant. It is updated only when an apartment is vacated or new residents move in.

Each row can be thought of as a record.

Starting with col D is where the reservation info is entered.

The code, so far, finds the proper resident's row after the user is prompted for an apartment number.

Then it finds the first empty cell in that row.

This is in col D if there have been no reservations previously made for that resident--otherwise it finds the first empty cell to the right.

There are 5 fields of info for each reservation. Appointment Date, Appointment Time, Doctor's or Facility"s Name, Town, Address.

[More to come, have to go to work]

The code that finds the proper row is fine so there's no need to change it.

The code for the Appointment Date is fine so there's no need to change it.

The code for the Appointment Time code is iffy so my solution was to create a userform and THAT is what I need help with.

I don't know how to get the userform to run. or where to put the code that gets it to run or even if the userform is correctly built.

How to send workbook sample and its vba code to this forum?

JOHNNYC

New member

Bob Phillips

Super Moderator

JOHNNYC

New member

Bob Phillips

Super Moderator

Bob Phillips

Super Moderator

JOHNNYC

New member

Bob Phillips

Super Moderator

JOHNNYC

New member

JOHNNYC

New member