Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Guys I need a little help - Excel folks wont help me

    Hello.

    I am well versed in Access and VBA so I should not drain anyones patience. I have hundreds of posts in the Access Forums.

    I have an application In MS Access that has to produce a spreadsheet that mimics the the Access.

    The data is repetitive:
    Project Manager End Date Project Name Budget Type Cost Type
    Forecast Hours
    Forecast Manpower ($)
    Forecast Exchg Rate Diff($)
    Forecast Travel Expenses
    Forecast Non-Travel Expenses
    Actual Hours
    Actual Manpower ($)
    Actual Exchg Rate Diff($)
    Actual Travel Expenses
    Actual Non-Travel Expenses
    Variance Hours
    Variance Manpower ($)
    Variance Exchg Rate Diff($)
    Variance Travel Expenses
    Variance Non-Travel Expenses

    I have written the code that makes the headers and the row headers (variance, actueals, etc) and actually fill in each cell with the data from access. My problem now is two fold
    1) I want to lock all rows but the rows that contain forecast hours, forecast travel expense, and forecast other. In those rows I want to trigger an event that when they tab out of the cell I look at sheet 2 on that sheet I need to find a cell that matches column A and B , get that value , multiply by the value in the cell I tabbed out of and place the new value in the forecast Manpower cvell.

    2) Part of me creating the spreadsheet is pasting some formatting and records. But I am getting a message when saving the wbook that says I have a large amount of data in the clipboard, do I want to save it. That message is stopping my code - how can i eliminate that message.
    Dale Houston, TX

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    1) I've done this before by naming ranges on the spreadsheet and using the Worksheet's Change event. If a change is made to a certain range, then some code will run, otherwise it's ignored. Here's a rough idea of how it works:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim strRange as String
      strRange = Target.Item(1).Name.Name
      Select Case strRange
      Case "BLAH"
        ...execute some code
      Case "WANKER"
        ...execute some other code
      Case else
         exit sub
    You'll also have to put in a public boolean variable that you'll use to keep the code from running when you're code is updating the spreadsheet.

    2) Application.CutCopyMode = False clears the clipboard
    Inspiration Through Fermentation

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    how would I use the function to trap for restoring values in a cell that I did not want edited.

    If myxlsheet.Cells(index1, index2) <> "Forecast" Then
    MsgBox "You cannot change this data.", vbExclamation, "Locked"
    Application.Undo
    Exit Sub
    end if

    when I do the Undo I get thrown in a loop because there was a change from the undo!
    Dale Houston, TX

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That's where that public boolean variable will come in
    Say it was called AUTOCHANGE. Then your WorkSheet_Change event would start with
    If AUTOCHANGE=True then Exit Sub

    To your code, you'll add this:

    If myxlsheet.Cells(index1, index2) <> "Forecast" Then
    MsgBox "You cannot change this data.", vbExclamation, "Locked"
    AUTOCHANGE=True
    Application.Undo
    AUTOCHANGE=False
    Exit Sub
    end if
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    That worked thank you. One more I promise

    sheet one has a column for hours
    after I place a value in the cell (SAY 8 FOR EXAMPLE) AND IT IS IN A COLUMN CALLED 200912(FOR DEC 09)

    IN COLUMN A IS THE NAME OF THE EMPLOYEE

    i WANT TO GET THE NAME (cOLUMN A) jOHN FOR EXAMPLE AND i WANT TO GET THE VALUE OF THE FIRST ROW OF MY COLUMN that had the 8) WHICH WOULD BE 200912 IN THIS CASE

    THEN GO TO SHEET2 FIND THE ROW WITH JOHN IN ONE CELL AND 200912 IN THE NEXT COLUMN OR CELL AND THEN GOT TO THE 3RD COLUMN AND GET THAT RATE VALUE

    ONCE i GET THAT RATE i MULTIPLY THE 8 * THE RATE AND TGHAT IS THE NEW VALUE FOR THE CELL I CHANGED
    Last edited by axsprog; 12-31-09 at 12:49.
    Dale Houston, TX

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The attached workbook has a lot of code for moving about in a spreadsheet and comparing values. It should contain all the commands you need to become familiar with. Just open the vbe ditor once you open the workbook and nose around.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This time with the attachment
    Attached Files Attached Files
    Inspiration Through Fermentation

Posting Permissions

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