Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Ivan-

  1. #1


    Ivan ,I wrote this question to you yesterday and it worked wonders! Thankyou. But I have one small problem with this. I wrote the problem at the bottom-
    original question-
    "Im curently using the following formula-Private Sub UserForm_Initialize()

    While ActiveCell.Value <> Int(Now)
    ActiveCell.Offset(1, 0).Select
    ListBox1.AddItem ActiveCell.Offset(0, 3).Value
    end sub

    This code written in the userform_initialize procedure is to locate the current date on the active sheet and list the text of 3 cells to the right of column "d" in the list box on the form. This works great! What Im trying to do is; using the same formula, find the same cell value, but on a diffrent sheet (Lets say "sheet2"). However I do not want that sheet ("sheet2") to show on the screen.
    My question is how do you add to the code to have the cell value on "Sheet2" show in the list box, but have the userform stay on the active sheet("sheet1") and not show "Sheet2"? "
    Your response-
    One way to do this;

    Dim Lb2, x As Double
    x = 1

    With Sheets("Sheet2")
    While Lb2 <> Int(Now)
    Lb2 = .Range("D1").Offset(x, 0)
    x = x + 1
    ListBox2.AddItem .Range("D1").Offset(x - 1, 3).Value
    End With


    My attached question-

    That userform that displays the list box(there are 8 listboxes pulling data from diffrent cells).But one of the list boxes does not use the offset method. I have to list a range (which identifies a sum 7 columns to the right of 31 dec 2002), but once the next year takes affect then I need that same list box to read a new sum in a cell that reads 7 cells to the right of 31 Dec 2003.
    My question would be how to write a code so that the list box knows once the end of the year is up, how can it then read the value in the next range?

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Auckland, New Zealand
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    To check the date do something like;
    the following before populating the list

    If Date > CDate("31/12/2003") Then
    'somecode if past the date
    'other code if not
    End If


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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