Results 1 to 9 of 9

Thread: Could someone please help me with a coding glitch (crossposted)?

  1. #1
    Seeker KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    14
    Articles
    0
    Excel Version
    365

    Could someone please help me with a coding glitch (crossposted)?



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

    I have a userform with checkboxes for each meal that a user is eligible to receive reimbursement for based on time calculations on my database. All of that is working appropriately, however, I have posted to another forum requesting assistance to lock down a checkbox if it isn't populated with a checkmark so the user isn't able to select it because the meal isn't allowed. The other forum user and I have tried several things and nothing seems to be working. I have confidence in him and I have no doubt that he is more than capable to answer, and it doesn't seem that the solution should be difficult, but I am also still new at VBA coding, so I am assuming that I am not relaying the information correctly? Here is the link to the 'crosspost' so you can see what has been done already: https://stackoverflow.com/questions/...kbox-questions I will gladly post the solution to my problem to that forum if someone here can assist me and if that is proper protocol.

    Here is the current coding that isn't working properly:

    Code:
     Private Sub txtArrivalTime_AfterUpdate()'When time is entered, time transfers immediately to spreadsheet datafield and sends back to userform which meals are allowed.
    
    
    Dim TargetRow As Integer
    TargetRow = Sheets("Codes").Range("D43").Value + 1
    
    
    With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 26)
     .Value = TimeValue(txtArrivalTime)
     .NumberFormat = "hh:mm" 'arrival time
    End With
    
    
    '''MEALS ALLOWED PER SPREADSHEET TO USERFORM'''
    If Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 28).Value = "T" Then
    frmUserTravel.chkMorning = Checked
    End If
    
    
    If frmUserTravel.chkMorning = Unchecked Then
       frmUserTravel.chkMorning.Enabled = False
    End If
    
    
    If Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 30).Value = "T" Then
    frmUserTravel.chkMidday = Checked
    End If
    
    
    If frmUserTravel.chkMidday = Unchecked Then
       frmUserTravel.chkMidday.Enabled = False
    End If
    
    
    If Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 32).Value = "T" Then
    frmUserTravel.chkEvening = Checked
    End If
    
    
    If frmUserTravel.chkEvening = Unchecked Then
       frmUserTravel.chkMidday.Enabled = False
    End If
    '''END MOVEMENT OF MEALS ALLOWED TO USERFORM'''
    
    
    End Sub

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,339
    Articles
    0
    Excel Version
    Office 365 Subscription
    The correct protocol is outlined here (there was a link to this in my previous message to you about cross-posting that you may have missed): https://www.excelguru.ca/content.php?184
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    14
    Articles
    0
    Excel Version
    365
    I don't understand what I missed? I noted that it was crossposted, I included the link, I stated that I had not received a working solution, I was respectful of all, would you please tell me what I missed?

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,339
    Articles
    0
    Excel Version
    Office 365 Subscription
    You suggested you weren't sure ("if that is proper protocol"). Sorry if I misinterpreted the comment.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    782
    Articles
    0
    Excel Version
    Excel 2010
    Hello KristenB

    The problem with showing us code that isn't working is that we don't know what it isn't working with.
    Any chance of you supplying a sample workbook indicative of what you're working with so anyone willing to assist doesn't have to guess at anything
    and can test possible solutions rather than posting something that you have to troubleshoot ?

  6. #6
    Seeker KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    14
    Articles
    0
    Excel Version
    365
    I can do that but please be aware, I have some code that I have commented out in my vba... I will remove it when I am completely finished but didn't want to remove it in the event I need it again. Also, I have taken a few hidden pages out of the form and removed some information to maintain confidentiality. So if something else doesn't work right, I'm sorry.
    Attached Files Attached Files

  7. #7
    Seeker KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    14
    Articles
    0
    Excel Version
    365
    @AliGW Okay... I just wanted to be sure if I was doing something wrong that I understood what. Thank you.

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    782
    Articles
    0
    Excel Version
    Excel 2010
    Does this get you any closer to what you're looking for
    Code:
    Private Sub txtArrivalTime_AfterUpdate()
    ' When time is entered, time transfers immediately to spreadsheet datafield and sends back to userform which meals are allowed.
    
    Dim TargetRow As Integer
    TargetRow = Sheets("Codes").Range("D43").Value + 1
    
    With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 26)
     .Value = TimeValue(txtArrivalTime)
     .NumberFormat = "hh:mm" 'arrival time
    End With
    
    '''MEALS ALLOWED PER SPREADSHEET TO USERFORM'''
    With Me.chkMorning
        If Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 28).Value = "T" Then
            .Value = Checked
        Else
            .Value = Unchecked
            .Enabled = False
        End If
    End With
    
    With Me.chkMidday
        If Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 30).Value = "T" Then
            .Value = Checked
        Else
            .Value = Unchecked
            .Enabled = False
        End If
    End With
    
    With Me.chkEvening
        If Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 32).Value = "T" Then
            .Value = Checked
        Else
            .Value = Unchecked
            .Enabled = False
        End If
    End With
    
    '''END MOVEMENT OF MEALS ALLOWED TO USERFORM'''
    
    End Sub

  9. #9
    Seeker KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    14
    Articles
    0
    Excel Version
    365
    Exactly what I needed! Thank you so much!!

Tags for this Thread

Posting Permissions

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