Solved

msflexgrid question

Posted on 2004-10-28
178 Views
Last Modified: 2013-12-26
I have a grid  with the ff columns

unit cost
total cost
unit price
total price

I want to make the values -numbers negative in that row alone

How is that done?

Thanks
0
Question by:zachvaldez
    16 Comments
     
    LVL 11

    Expert Comment

    by:dbrckovi
    Hi!

    If I inderstood properly, you want to make all values in single row negative.
    If so, then try this:
    '------------------------------------------------
    Private Sub Command1_Click()
        MakeNegative MSFlexGrid1, 3
    End Sub

    Sub MakeNegative(GridName As MSFlexGrid, RowNumber As Long)
       
        If RowNumber >= GridName.Rows Then
            MsgBox "Invalid Row"
        Else
            For X = 0 To GridName.Cols - 1
                GridName.TextMatrix(RowNumber, X) = 0 - Val(GridName.TextMatrix(RowNumber, X))
            Next X
        End If
    End Sub
    '---------------------------------------------------

    If this is not what you need, then please explain a bit more.
     - What is 'ff' ?
     - Are unit cost, total cost, etc.,  Column headers or Row headers?
     - What does "values -numbers" mean?
    0
     

    Author Comment

    by:zachvaldez
    Are unit cost, total cost, etc.,  Column headers or Row headers? -- Columnheaders

    What does "values -numbers" mean - input are numbers

    I idea is to make the numbers appear negative in that row selected as if you are giving a discount for that row


    0
     

    Author Comment

    by:zachvaldez
    Why am is the rownumber hardocoded here?

    Private Sub Command1_Click()
        MakeNegative MSFlexGrid1, 3 ---->    Can it be identified based on row selected instead of hardcodind it...?
    End Sub
    0
     
    LVL 6

    Expert Comment

    by:PePi
    'declare a modulelevel variable
    Private fRow as Integer, fCol as Integer


    Private Sub flxGrid_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
        With flxGrid
            If .MouseRow = 0 Then fRow = 0: Exit Sub

            fRow = .MouseRow: fCol = .MouseCol
        End With
    End Sub

    Private Sub flxGrid_Click()
        If fRow = 0 Then Exit Sub       'don't process if header was clicked

        With flexGrid
            If Not IsNumeric(.TextMatrix(fRrow, fCol)) Then
                MsgBox "Cannot Convert! Data Not Numeric.", vbOkOnly+vbExclamation, "Conversion Error"
                Exit Sub
            End if

            'continue with conversion
            .Redraw = False
            .TextMatrix(fRow, fCol) = "-" & .TextMatrix(fRow, fCol)
            .Redraw = True
        End With
    End Sub

    HTH!!!

    0
     

    Author Comment

    by:zachvaldez
    Specifically, the location I want to convert are in col 7 & col 9

    col 7 is unit cost col 9  unit price

    However I do not make all of the values in this column negative only The selected line row.

    col 1 to 6 are text ::
    0
     
    LVL 6

    Expert Comment

    by:PePi
    when you mean Columns 7 or 9, is that 0 based? i mean in your flexgrid, the first column is actually Col = 0. I assumed that by columns 7 and 9, they are 0 based. If not, just change 7 to 6 and 9 to 8

    Private Sub flxGrid_Click()
        If fRow = 0 Then Exit Sub       'don't process if header was clicked

        With flexGrid
            If fCol = 7 Or fCol = 9 Then
                If Not IsNumeric(.TextMatrix(fRrow, fCol)) Then
                    MsgBox "Cannot Convert! Data Not Numeric.", vbOkOnly+vbExclamation, "Conversion Error"
                    Exit Sub
                End if

                'continue with conversion
                .Redraw = False
                .TextMatrix(fRow, fCol) = "-" & .TextMatrix(fRow, fCol)
                .Redraw = True
            End If
        End With
    End Sub
    0
     

    Author Comment

    by:zachvaldez
    When it hits this line it exits...

     If fCol = 7 Or fCol = 9 Then
    0
     
    LVL 6

    Expert Comment

    by:PePi
    add this line before the if statement

    MsgBox "fCol = " & fCol


    tell me what column you cliked and what was displayed
    0
     
    LVL 6

    Expert Comment

    by:PePi
    i guess Col 7 is actually Col 6 in 0 base, and Col 9 is Col 8 also in 0 base. to verify this, click on column 7 and if the display is "fCol = 6" then change the IF statement to:

    If fCol = 6 Or fCol = 8 Then


    0
     

    Author Comment

    by:zachvaldez
    This is what I improvise out of your code and let me know if you can improve it...

    It seems that it is changing those numbers!


     Dim iCols As Integer
     
       
        For iCols = 0 To Grid1.Cols - 1
            With Grid1
               
                If iCols = 7 Then
                    .TextMatrix(.row, 7) = .TextMatrix(.row, iCols) * -1
                   
                End If
                If iCols = 9 Then
                    .TextMatrix(.row, 9) = .TextMatrix(.row, iCols) * -1
                   
                End If
            End With
        Next iCols
    0
     
    LVL 6

    Expert Comment

    by:PePi
    i thought you want it to change only when the row is clicked? which event did you put this code in?
    0
     

    Author Comment

    by:zachvaldez
    command_click

    Call ChangeNumtoNegative


    end sub

    Isa it possible to do this in one line...

             If iCols = 7 Then
                    .TextMatrix(.row, 7) = .TextMatrix(.row, iCols) * -1
                   
                End If
                If iCols = 9 Then
                    .TextMatrix(.row, 9) = .TextMatrix(.row, iCols) * -1
                   
                End If
            End With
    0
     
    LVL 6

    Expert Comment

    by:PePi
    yes,

    if iCols = 7 or iCols = 9 then

    end if
    0
     
    LVL 6

    Accepted Solution

    by:
    ooops

    yes,

    here's the one line


    if iCols = 7 or iCols = 9 then .TextMatrix(.Row, iCols) = Val(.TextMatrix(.Row, iCols)) * -1
    0
     
    LVL 6

    Expert Comment

    by:PePi
    you can also change your for next loop statement to:


    For iCols = 7 To 9 Step 2

    Next iCols

    coz you know for a fact that you only need to process columns 7 and 9. you don't have to go thru every column
    0
     

    Author Comment

    by:zachvaldez
    Thanks! Thas was a great learning tip!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now