+ Reply to Thread
Results 1 to 2 of 2

Tab Map and Offset Function

  1. #1
    Registered User
    Join Date
    05-16-2011
    Location
    Virginia Beach, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    22

    Post Tab Map and Offset Function

    Funding.xls
    Please help me find my error with the below code for the attached Workbook.

    Worksheet Password : pass
    Sheet 1 is EC Template with Form to be filled out by User
    Sheet 2 is the desired Tab Order/Map

    Sheet is protected to prevent overwriting form titles and establish a easier entry path...

    Sub Worksheet_Change(ByVal Target As Range)
    '******************************************************************************************
    'Function to get a variable Dollar value from User when the Contract Line Item (CLIN) number
    'in Range ("A17:A28") equals 0217.
    '
    'Amount will be transferred to CLIN table cell for CLIN 0217 ("AC9")
    '
    '*******************************************************************************************

    Dim Msg As String 'Message line to get User input
    Dim FxdCost As Double 'Variable to hold Fixed Cost input from User
    Dim VRange As Range 'Name Range for CLIN Column that will be incremented through looking for specific value
    Dim cell As Range

    'Initialize variable
    Set VRange = Range("a17:a28")

    If Intersect(VRange, Target) Is Nothing Then
    Exit Sub
    For Each cell In Intersect(VRange, Target)

    'Test for CLIN 0217
    If cell.Value = "0217" Then

    'Prompt User for request amount to fund

    FxdCost = InputBox("Enter the Amount of Fixed Cost you wish to fund ")
    'Establish Quantity of Fixed Cost to default value of 1

    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    'This is where the problem lays. If the User presses the Enter key the active cell is below and if
    'the User presses the Tab key the active cell is to the right. I can establish a notice to use the Tab key, however
    'I don't want the User to be taken to the Lookup Tables in the shaded section to the right.
    '
    'The Lookup Tables have to remain unprotected to allow changes/additions/deletions.
    '
    'Because I 'm not sure on what line within the Range("A17:A28")the User will enter the CLIN 0217 I would need
    'to use the Offset function.
    '
    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    '
    'Once I get the Offset to be correct, I want the Quantity to be a default value of 1 for CLIN 0217 and have
    'the font color match the interior. That doesn't seem to be working.

    'ActiveCell.Value = "1"
    'ActiveCell.Font.Color = ActiveCell.Interior.ColorIndex

    'ActiveCell.Offset(0, 3).Font.ThemeColor = xlThemeColorDark1

    'Populate CLIN Table

    Range("ac9").Value = FxdCost
    End If

    Next cell

    End Sub

    -----------------------------------------------------------------------------------------------------------------

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    '***************************************************************************************
    'Tab Cell Map designed to flow through the EC Template sheet without tabbing into the
    'Lookup tables.
    '***************************************************************************************

    'Declare Variables
    Dim TabCellMap As Range, cell As Range, Fn As Object, x As Integer


    Application.EnableEvents = False

    'Initialize
    Set Fn = Application.WorksheetFunction
    Set TabCellMap = Sheet2.Cells.SpecialCells(xlConstants, xlNumbers)

    x = 1

    'Error Correction call
    On Error GoTo errhand
    If Target.Row < LastAddr.Row Or Target.Column < LastAddr.Column Then x = -1 Else x = 1

    'Start the Map
    n = n + x
    If n = Fn.Max(TabCellMap) + 1 Then n = 1
    If n < 1 Then n = Fn.Max(TabCellMap)
    'Continue loop
    For Each cell In TabCellMap
    If cell.Value = n Then
    TabAddr = cell.Address
    Exit For
    End If
    Next cell

    Sheet1.Range(TabAddr).Select
    Application.EnableEvents = True

    Exit Sub
    errhand:
    Set LastAddr = Target
    Resume
    End Sub
    Sub enable_events()
    Application.EnableEvents = True
    End Sub

    Any and all help is deeply appreciated.

    Thanks!
    Ambassador777

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Tab Map and Offset Function

    To be able to find out whats wrong with the code, we need to know what your code is trying to do. Can you explain briefly?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1