To create a new dialog box

ganeshdlegend · #1 (**permalink**) 02-24-09, 04:02

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

gvee · #2 (**permalink**) 02-26-09, 05:58

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)

ganeshdlegend · #3 (**permalink**) 02-26-09, 23:57

George Thank you very much indeed!!!

ganeshdlegend · #4 (**permalink**) 02-27-09, 03:17

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

gvee · #5 (**permalink**) 02-27-09, 05:16

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

ganeshdlegend · #6 (**permalink**) 02-27-09, 07:33

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,

gvee · #7 (**permalink**) 03-02-09, 03:29

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.

ganeshdlegend · #8 (**permalink**) 03-02-09, 06:10

Hi Goerge,

No worries. Thank you!

ganeshdlegend · #9 (**permalink**) 03-04-09, 05: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,

Chris Bode · #10 (**permalink**) 03-05-09, 02:04

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

ganeshdlegend · #11 (**permalink**) 03-05-09, 09:19

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,

gvee · #12 (**permalink**) 03-05-09, 09:33

Ganesh,

Are you able to assign a value to those boxes programatically yourself?
Something like

Code:

control_name.value = "test"

ganeshdlegend · #13 (**permalink**) 03-05-09, 10:52

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,

Chris Bode · #14 (**permalink**) 03-06-09, 01:15

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

gvee · #15 (**permalink**) 03-06-09, 05:40

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

Internet Services

Web Development

Digital Marketing

Consumer Tech