Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    UnCalculated values (VB6 and Excel 2000)

    I have a VB6 program that I use to record information. I then take that
    information and insert it into a pre-formatted worksheet. The problem
    is that after inserting the data some of the formulas in the spreadsheet
    do not seem to kick in unless I go to the specific cell press F2 and
    then hit the enter key. After doing this the alignment changes to what
    it should be and the formula works.

    In another page in the worksheet data captured in a text box in VB appears
    in the work sheet but the formula that is supposed to add the numbers up doesn't
    seem to work.

    I've tried setting the DataFormat property of the text box to number, I've
    done a "CInt" on the value contained in the variable for the text box and
    still I can't get the formula to do what it is supposed to do.

    Why can't I get it to use the correct alignment and calculate the formula?

    Frowning and Frustrated,
    John

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UnCalculated values (VB6 and Excel 2000)

    Clearly Excel thinks the cell contains text.

    Could you post the relevant bits of code, especially the line that assigns the value to the Cell?

    If you enter this formula in the Excel sheet:

    =ISNUMBER(CellThatReceivedTextBoxValue)

    What does it say?
    and wat with

    =ISTEXT(CellThatReceivedTextBoxValue)

    What happens on both if you press F2-enter on the "CellThatReceivedTextBoxValue" ?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UnCalculated values (VB6 and Excel 2000)

    Thanks Jan,

    You hit the nail on the head! Excel is seeing the values as Text not Number.
    Here is a bit of the code...
    With objThisWrkBk.Sheets(strShtName)
    .Cells(5, 3).Select
    .Cells(5, 3).Value = txtProductNm(0)
    .Cells(6, 4).Value = txtProductSz(0)
    .Cells(7, 6).Value = intBarCode1(0)
    .Cells(7, 7).Value = intBarCode2(0)
    .Cells(8, 2).Value = txtLotNum(0)
    .Cells(9, 3).Value = dtExpDate(0)
    .Cells(12, 1).Select
    .Cells(10, 5).Value = intUnitsP_Car(0)
    .Cells(11, 4).Value = intCarP_Skid(0)
    .Cells(15, 4).Select
    .Cells(15, 4).Value = txtDateReceived(0)
    .Cells(16, 2).Value = cboTransCo(0)
    .Cells(21, 4).Select
    .Cells(21, 4).Value = cboRecvdBy(0)
    End With

    The following two variables are the ones that are giving me the problem.

    intUnitsP_Car(0)
    intCarP_Skid(0)

    How do I resolve this?
    I suppose that I could do something like:

    intA = CIntintUnitsP_Car(0)
    .Cells(11, 9).Value = intA
    intA = 0

    or

    Cells(daRow, 9).Select
    intA = CInt(intUnitsPerDisplay(i))
    intUnitsPerDisplay(i) = intA
    intA = 0
    ActiveCell.Value = intUnitsPerDisplay(i)

    but neither way seems to work.

    Any suggestions?

    John

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UnCalculated values (VB6 and Excel 2000)

    A few remarks:

    Remove the lines the select a cell, they are unnecesary.

    Then try this with your offending cells:

    .Cells(10, 5).NumberFormat = "General"
    .Cells(10, 5).Value = intUnitsP_Car(0)
    .Cells(11, 4).NumberFormat = "General"
    .Cells(11, 4).Value = intCarP_Skid(0)

    I assume "int" means both variables are declared as an array of integers?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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