Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2009
    Posts
    24

    Unanswered: To create a new dialog box

    Hi Techies!,

    I'm not technically trained in writing program in visual basic. I'm aiming to write a macro in VB, to display the information in excel in a new dialog box at a click of the button.

    for e.g; sum(A11:A30) should be displayed in a new dialog box.

    can someone help me
    Ganesh
    Everyone is gifted! Some open the package sooner!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim rng As Range
    
    With ActiveSheet
        Set rng = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    MsgBox Application.WorksheetFunction.Sum(rng)
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Posts
    24
    George Thank you very much indeed!!!
    Ganesh
    Everyone is gifted! Some open the package sooner!

  4. #4
    Join Date
    Feb 2009
    Posts
    24

    To create a dialog box

    George,

    Thank you.

    The code you have given is working fine. But it looks so simple. I have created a structure of the dialog box as how it should appear. pls find the same in the attached word file.

    i will await your reply.

    thx
    Attached Files Attached Files
    Ganesh
    Everyone is gifted! Some open the package sooner!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't know how to assign values to controls in Excel, check out the helpfiles.
    This code should hel you on your way though
    Code:
    Dim a As Integer
    Dim g As Integer
    
    'Sheet 1                                          \|/
    a = Application.WorksheetFunction.Sum(Sheets("sheet1").Range("A1:A10"))
    g = Application.WorksheetFunction.Sum(Sheets("sheet1").Range("G1:G10"))
    
    MsgBox "A = " & a
    MsgBox "G = " & g
    MsgBox "Diff: " & a - g
    
    'Sheet 2                                          \|/
    a = Application.WorksheetFunction.Sum(Sheets("sheet2").Range("A1:A10"))
    g = Application.WorksheetFunction.Sum(Sheets("sheet2").Range("G1:G10"))
    
    MsgBox "A = " & a
    MsgBox "G = " & g
    MsgBox "Diff: " & a - g
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2009
    Posts
    24

    to create a new dialog box

    George,

    Thank you. It is giving the result only for sheet1 and not for sheet2.

    I expect the result to be in the form and layout i have prescribed in the word document.

    regards,
    Ganesh
    Everyone is gifted! Some open the package sooner!

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hi Ganesh,

    I'm afraid the above is the extent of my Excel knowledge - hopefully you can work with what's been provided so far and complete the last steps.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2009
    Posts
    24
    Hi Goerge,

    No worries. Thank you!
    Ganesh
    Everyone is gifted! Some open the package sooner!

  9. #9
    Join Date
    Feb 2009
    Posts
    24
    Hello Marsh,

    Thanks for your reply.

    Cud you pls be more specific on your description?

    I don't understand what you have specified.

    regards,
    Ganesh
    Everyone is gifted! Some open the package sooner!

  10. #10
    Join Date
    Feb 2009
    Posts
    6
    1.Right click on the toolbar, from the popup select control box
    2.From the control box that appears on the sheet1, select and draw a command button on the sheet1
    3.Double click the command button to open the code window and paste following codes

    Code:
    Private Sub CommandButton1_Click()
        Dim row As Integer, col As Integer
        row = 11
        col = 1
        
        Dim sum As Double
        sum = 0
        
        For row = 11 To 30
            sum = sum + CDbl(Sheet1.Cells(row, col).Value)
        Next
        
        MsgBox sum
    End Sub

  11. #11
    Join Date
    Feb 2009
    Posts
    24
    Chris,

    Thanks for your reply.

    I expect the appearance of the dialog box to be in the form i have prescribed in the word document...

    cud you pls help me?

    regards,
    Ganesh
    Everyone is gifted! Some open the package sooner!

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ganesh,

    Are you able to assign a value to those boxes programatically yourself?
    Something like
    Code:
    control_name.value = "test"
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2009
    Posts
    24
    George,

    I'm sorry... I donot know how to assign values to the controls.... I'm completely ignorant...

    It is not nessasary that the dialog box should be in the same structure i have mentioned in the word dcument. It have just conveyed my idea...

    You can rework and bring it to your convenience as well. But it should convey the SUM(A1:A10) and SUM(G1:G10) and the difference in one single msg box but not sequential.

    I'm glad that you are looking forward to help me again...

    Thanks,
    Ganesh
    Everyone is gifted! Some open the package sooner!

  14. #14
    Join Date
    Feb 2009
    Posts
    6
    Use following code for the design of the user form1 that you have given in word document

    Code:
    Private Sub cmdCancel_Click()
        Unload Me
    End Sub
    
    Private Sub cmdOK_Click()
        txtSumA1A10.Text = CStr(sheet1SumA1A10)
        txtSumG1G10.Text = CStr(sheet1SumG1G10)
        
        txtDiffSheet1.Text = CStr(sheet1SumA1A10 - sheet1SumG1G10)
        
        txtSheet2SumA1A10.Text = CStr(sheet2SumA1A10)
        txtSheet2SumG1G10.Text = CStr(sheet2SumG1G10)
        
        txtDiffSheet2.Text = CStr(sheet2SumA1A10 - sheet2SumG1G10)
    End Sub
    
    Private Function sheet1SumA1A10() As Double
        Dim row As Integer, col As Integer
        col = 1
        
        Dim sum As Double
        sum = 0
        For row = 1 To 10
            sum = sum + CDbl(Sheet1.Cells(row, col).Value)
        Next
        sheet1SumA1A10 = sum
    End Function
    
    Private Function sheet1SumG1G10() As Double
        Dim row As Integer, col As Integer
        col = 7
        
        Dim sum As Double
        sum = 0
        For row = 1 To 10
            sum = sum + CDbl(Sheet1.Cells(row, col).Value)
        Next
        sheet1SumG1G10 = sum
    End Function
    
    Private Function sheet2SumA1A10() As Double
        Dim row As Integer, col As Integer
        col = 1
        
        Dim sum As Double
        sum = 0
        For row = 1 To 10
            sum = sum + CDbl(Sheet2.Cells(row, col).Value)
        Next
        sheet2SumA1A10 = sum
    End Function
    
    Private Function sheet2SumG1G10() As Double
        Dim row As Integer, col As Integer
        col = 7
        
        Dim sum As Double
        sum = 0
        For row = 1 To 10
            sum = sum + CDbl(Sheet2.Cells(row, col).Value)
        Next
        sheet2SumG1G10 = sum
    End Function

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim a As Integer
    Dim g As Integer
    
    Dim msg As String
    msg = ""
    
    a = Application.WorksheetFunction.Sum(Sheets("sheet1").Range("A1:A10"))
    g = Application.WorksheetFunction.Sum(Sheets("sheet1").Range("G1:G10"))
    
    msg = msg & "Sheet1(A1:A10) = " & CStr(a) & Chr(13)
    msg = msg & "Sheet1(G1:G10) = " & CStr(g) & Chr(13)
    msg = msg & "Difference     = " & CStr(a - g) & Chr(13)  & Chr(13)
    
    a = Application.WorksheetFunction.Sum(Sheets("sheet2").Range("A1:A10"))
    g = Application.WorksheetFunction.Sum(Sheets("sheet2").Range("G1:G10"))
    
    msg = msg & "Sheet2(A1:A10) = " & CStr(a) & Chr(13)
    msg = msg & "Sheet2(G1:G10) = " & CStr(g) & Chr(13)
    msg = msg & "Difference     = " & CStr(a - g)
    
    MsgBox msg
    George
    Home | Blog

Posting Permissions

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