Results 1 to 9 of 9

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

  1. #1
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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.

  3. #3
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    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?
    Last edited by Ken Puls; 2012-07-14 at 06:03 AM. Reason: Attachment removed at users's request.

  4. #4
    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.

  5. #5
    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.

  6. #6
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    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.

  7. #7
    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?

  8. #8
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    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]

  9. #9
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    Quote Originally Posted by JOHNNYC View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •