[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I require fields to be completed before they submit (VB.Net)?

Posted on 2004-11-30
20
Medium Priority
?
371 Views
Last Modified: 2012-06-22
I wrote a program that takes user input and puts it into an existing excel spreadsheet.

it consists of 3 listboxes and 4 textboxes.  

However if they do not fill in a textbox or select something in the listbox the data does not go to the correct cell. I want to require that ALL fields be completed before they can update the Excel sheet.  Here's my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        objApp = New Excel.Application
        objApp.Visible = False
        objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=False)

        Dim newRow As Integer

        newRow = objBook.ActiveSheet.UsedRange.Rows.Count + 1

        With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeLeft)
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .Weight = Excel.XlBorderWeight.xlMedium
            .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
        End With
        With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeTop)
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .Weight = Excel.XlBorderWeight.xlThin
            .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
        End With
        With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeBottom)
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .Weight = Excel.XlBorderWeight.xlThin
            .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
        End With
        With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeRight)
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .Weight = Excel.XlBorderWeight.xlMedium
            .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
        End With
        With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlInsideVertical)
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .Weight = Excel.XlBorderWeight.xlMedium
            .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
        End With
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 1) = DateTimePicker1.Value
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 1).NumberFormat = "mm/dd/yyyy h:mm"
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 1).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 5) = strMethod
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 5).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 6) = TextBox1.Text
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 6).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 7) = TextBox2.Text
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 7).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 8) = strYourName
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 8).horizontalalignment = Excel.XlHAlign.xlHAlignCenter
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 9) = TextBox3.Text
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 9).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 10) = strLoc
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 10).font.colorindex = 5
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 10).font.underline = True
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 10).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 11) = TextBox4.Text

        objBook.SaveAs(Filename:="K:\RT_DCX\Receivefiles2004_1.xls")
        objBook.Close()
        objApp.Quit()
        Dim p() As Process = Process.GetProcessesByName("EXCEL")

        'kills all processes older than 5 seconds
        Dim i As Integer
        For i = 0 To p.Length - 1
            If ((p(i).StartTime.AddSeconds(5) < DateTime.Now)) Then
                p(i).Kill()
            End If
        Next
        Dim response As MsgBoxResult
        If MsgBox("Do you want to view the document?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            objApp = New Excel.Application
            objApp.Visible = True
            objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
        Else
            End
        End If
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        objApp = New Excel.Application
        objApp.Visible = True
        objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim p() As Process = Process.GetProcessesByName("EXCEL")

        'kills all processes older than 3 seconds
        Dim i As Integer
        For i = 0 To p.Length - 1
            If ((p(i).StartTime.AddSeconds(5) < DateTime.Now)) Then
                p(i).Kill()
            End If
        Next
        End
    End Sub
End Class
0
Comment
Question by:lkingpinl
  • 9
  • 7
  • 2
  • +1
20 Comments
 
LVL 22

Expert Comment

by:cookre
ID: 12710825
A very common way is to check each field explicitly in the code triggered by the submit.
0
 
LVL 1

Expert Comment

by:jaigan1979
ID: 12713475
BEFORE STARTING TO WRITE IN HTE EXCEL SHEET US THIS PIECE OF CODE


DIM CTRL1 AS VARIANT
FOR EACH CTRL1 IN ME.CONTROLS
   
    IF ME.TEXT = "" THEN



NEXT CTRL1
0
 
LVL 1

Expert Comment

by:jaigan1979
ID: 12713478
BEFORE STARTING TO WRITE IN THE EXCEL SHEET US THIS PIECE OF CODE


DIM CTRL1 AS VARIANT
FOR EACH CTRL1 IN ME.CONTROLS
   
    IF ME.TEXT = "" THEN
         MSGBOX "PLEASE ENTER ALL THE FIELDS"
         EXIT SUB
    END IF

NEXT CTRL1

HOPE THIS WORKS...LET ME KNOW YOUR COMMENTS...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Expert Comment

by:Snarf0001
ID: 12717633
First, if you're not using a lot of different controls, cookre's suggestion will yeild better performance.  If you do have a lot of controls, and don't want sloppy code with a giant list of if's to do the checking, that's when jaigan's code could be used, but there are a few problems with it.

#1, in vb.net, controls are in a heirarchy, so if you have a groupbox on a form (for example) and then textboxes in the group box, calling me.controls will return ONLY the direct child controls in itself (being the group box).  It will not return the child controls of the group box, being the textboxes you need to check.

#2, declare CTRL1 as Control, not variant, performance will be significantly better

#3, type checking should be done, because if there are any other controls on the form such as a radio button, picturebox... that you haven't specified the .Text property for, the messagebox will still be displayed.

Code should be something like this:

    Private Function ValidateControls(ByVal ctrl As Control) As Boolean

        Dim c As Control, bReturn As Boolean = True

        For Each c In ctrl.Controls()
            Select Case c.GetType.Name
                Case "TextBox"
                    If (c.Text = "") Then Return False
                Case "ComboBox"
                    If (CType(c, ComboBox).SelectedIndex = -1) Then Return False
                Case "Panel", "GroupBox", "TabControl"
                    'to be cheap, replace the above with simply case else
                    'instead of the "Panel", "GroupBox"...
                    'to be thorough, add any more control types that might contain child controls
                    bReturn = ValidateControls(c)
            End Select
        Next

        Return (bReturn)

    End Function

and to actually start the check, call

        ValidateControls(Me)
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12717654
sorry, call

        If (Not (ValidateControls(Me))) Then
            MsgBox("enter all values")
        End If
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12717691
sorry, that was messy, should be:

    Private Function ValidateControls(ByVal ctrl As Control) As Boolean

        Dim c As Control, bReturn As Boolean = True

        For Each c In ctrl.Controls()
            Select Case c.GetType.Name
                Case "TextBox"
                    If (c.Text = "") Then Return False
                Case "ComboBox"
                    If (CType(c, ComboBox).SelectedIndex = -1) Then Return False
                Case "Panel", "GroupBox", "TabControl"
                    'to be cheap, replace the above with simply case else
                    'instead of the "Panel", "GroupBox"...
                    'to be thorough, add any more control types that might contain child controls
                    If (Not (ValidateControls(c))) Then Return False
            End Select
        Next

        Return True

    End Function
 
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12725557
Snarf,

The code worked.  It puts them in the correct boxes now, however, I want to require to select an item from each listbox and fill in each textbox before it updates the excel document.

Thanks for all your help.
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12726337
That's what the code should be doing:

                   If (c.Text = "") Then Return False

if a textbox is blank, return false

                   If (CType(c, ComboBox).SelectedIndex = -1) Then Return False

if a combobox has no selectedindex, return false

If everything is selected, the function will return true, if anything fails, the function will return false.
if it returns false, put up a message saying they didn't fill everything in, and don't update excel.

Is something else happening?
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12726661
I ran a test and only selected something from one of the listboxes and input text in only 1 textbox.

I submitted the update and it updated the Excel document accordingly.  No message, nothing.  I need to do like a check when button1 is clicked that text is "" then msgbox (Please fill in all fields), and do the same thing for the listboxes selected index.  
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12736602
I tried Jaigan's suggestion and it does not work either.

I did add If..Then MsgBox() statements for each textbox, and they show up but it continues to add the data to excel regardless.  I need it so if they do not select something in a list box or miss typing something in a textbox it pops up saying "All fields are required" and DOES NOT execute the remainder of the code.  

Any ideas?
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12736732
points raised
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12736773
You need to still put the excel code into the if logic...

Change button1 click to this:

       If (Not (ValidateControls(Me))) Then
            MsgBox("enter all values")
        Else
            everything else you have in button1 click to perform the excel logic
        End If

Calling messagebox will not stop the excel code from triggering, you still need to stop that in an "else" yourself.
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12737187
It works for the Textboxes now, but if I don't select something in one of the Listboxes, it still executes the code.  Here's the snippet of code:

Private Function ValidateControls(ByVal ctrl As Control) As Boolean

        Dim c As Control, bReturn As Boolean = True

        For Each c In ctrl.Controls()
            Select Case c.GetType.Name
                Case "TextBox"
                    If (c.Text = "") Then Return False
                Case "Listbox"
                    If (CType(c, ListBox).SelectedIndex = -1) Then Return False
                Case Else
                    If (Not (ValidateControls(c))) Then Return False
            End Select
        Next

        Return True

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If (Not (ValidateControls(Me))) Then
            MsgBox("You must enter all values!")
        Else
        'Excel data entry and formatting stuff here
       End If
End Sub
0
 
LVL 22

Expert Comment

by:cookre
ID: 12737915
Perhaps what you're missing is resetting the focus to a field when it's found to be invalid.  E.g.:

in the Submit/OK/Whatever button handler:

if FieldX is invalid or empty or otherwas a badboy then
   give a message saying FieldX was bad and has to be corrected
   FieldX.SetFocus()

That puts the cursor back in the bad field instead of continuing processing as if all was OK.
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12739183
This is the snippet of code that is not working:

Case "Listbox"
                    If (CType(c, ListBox).SelectedIndex = -1) Then Return False

No error or anything, but it does not what it is supposed to.  It should require the user to select something in the listbox before continuing, but it doesn't.  Why?
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12741569
Sorry, case is wrong, should be "ListBox", not "Listbox".  Capitalize the "B".
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12758083
tried that, still no success.
0
 
LVL 23

Accepted Solution

by:
Snarf0001 earned 1500 total points
ID: 12758643
I didn't actually notice that you had changed it from a ComboBox to a ListBox, for a list box, this would be a better method:

                Case "ListBox"
                    If (CType(c, ListBox).SelectedIndices.Count = 0) Then Return False

but .SelectedIndex would still work.  Are these just normal listboxes you're using?  Not checkedlistboxes?  Can you post more of your code?
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12763272
They are normal listboxes.  I updated the code, ran the program and filled in every textbox and selected nothing for the listboxes and I got an error:

An unhandled exception of type 'System.InvalidCastException' occurred in Receivefiles_updater.exe

Additional information: Specified cast is not valid.

Doesn't highlight anything.  So it's still not working.  I'll post my code.  (raising points to 500 also)
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12763283
Here's my entire code:




Imports Excel
Public Class Form1
    Inherits System.Windows.Forms.Form


    Dim objApp As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheets As Excel.Worksheets
    Dim objSheet1 As Excel.Worksheet
    Dim Range As Excel.Range
    Dim strMethod As String
    Dim strLoc As String
    Dim strYourName As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim p() As Process = Process.GetProcessesByName("EXCEL")

        'kills all processes older than 3 seconds
        Dim i As Integer
        For i = 0 To p.Length - 1
            If ((p(i).StartTime.AddSeconds(5) < DateTime.Now)) Then
                p(i).Kill()
            End If
        Next
        'add items to listbox1
        ListBox1.Items.Add("VPM")
        ListBox1.Items.Add("Autoweb")
        ListBox1.Items.Add("HBPO FTP Site")
        ListBox1.Items.Add("Customer FTP")
        ListBox1.Items.Add("Behr Website")
        ListBox1.Items.Add("Disk")
        ListBox1.Items.Add("E-mail")

        'add items to listbox2
        ListBox2.Items.Add("Brian Walker")
        ListBox2.Items.Add("Dave Williams")
        ListBox2.Items.Add("Krishna Reddy")
        ListBox2.Items.Add("Martin Valach")
        ListBox2.Items.Add("Matt Weber")
        ListBox2.Items.Add("Tony Hughes")

        'add items to listbox3
        ListBox3.Items.Add("AC-LINES")
        ListBox3.Items.Add("AIR-INDUCTION")
        ListBox3.Items.Add("ALTERNATOR")
        ListBox3.Items.Add("AMBIENT_AIR_SENSOR")
        ListBox3.Items.Add("BIW")
        ListBox3.Items.Add("BUMPER")
        ListBox3.Items.Add("COOLING_MODULE")
        ListBox3.Items.Add("CRASH_SENSORS")
        ListBox3.Items.Add("CRUISE_CONTROL_MOD")
        ListBox3.Items.Add("DESIGN_STUDIO")
        ListBox3.Items.Add("ENGINE")
        ListBox3.Items.Add("EXHAUST")
        ListBox3.Items.Add("FACIA")
        ListBox3.Items.Add("FASTENERS")
        ListBox3.Items.Add("FENDERS")
        ListBox3.Items.Add("FUEL")
        ListBox3.Items.Add("GROUND_LINE")
        ListBox3.Items.Add("HEADLAMPS")
        ListBox3.Items.Add("HOOD")
        ListBox3.Items.Add("HORN")
        ListBox3.Items.Add("HOSES")
        ListBox3.Items.Add("INCOMING")
        ListBox3.Items.Add("LATCH")
        ListBox3.Items.Add("LOADBEAMS")
        ListBox3.Items.Add("LWR_CROSSMEMBER")
        ListBox3.Items.Add("OIL_RES")
        ListBox3.Items.Add("PWR-STRG-ASSY")
        ListBox3.Items.Add("SIDE_RAILS")
        ListBox3.Items.Add("SSR")
        ListBox3.Items.Add("STANDARD_PARTS")
        ListBox3.Items.Add("TIPM")
        ListBox3.Items.Add("TOC_LINES")
        ListBox3.Items.Add("TPMS")
        ListBox3.Items.Add("UNITS-SENSORS")
        ListBox3.Items.Add("WIRE_HARNESS")
    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        Select Case ListBox1.SelectedIndex
            Case 0
                strMethod = "VPM"
            Case 1
                strMethod = "AutoWeb"
            Case 2
                strMethod = "HBPO FTP"
            Case 3
                strMethod = "Cust FTP"
            Case 4
                strMethod = "Behr Website"
            Case 5
                strMethod = "Disk"
            Case 6
                strMethod = "E-mail"
        End Select
    End Sub

    Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
        Select Case ListBox2.SelectedIndex
            Case 0
                strYourName = "Brian Walker"
            Case 1
                strYourName = "Dave Williams"
            Case 2
                strYourName = "Krishna Reddy"
            Case 3
                strYourName = "Martin Valach"
            Case 4
                strYourName = "Matt Weber"
            Case 5
                strYourName = "Tony Hughes"
        End Select
    End Sub

    Private Sub ListBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox3.SelectedIndexChanged
        Select Case ListBox3.SelectedIndex
            Case 0
                strLoc = "K:\CATIA\AC-LINES"
            Case 1
                strLoc = "K:\CATIA\AIR-INDUCTION"
            Case 2
                strLoc = "K:\CATIA\ALTERNATOR"
            Case 3
                strLoc = "K:\CATIA\AMBIENT_AIR_SENSOR"
            Case 4
                strLoc = "K:\CATIA\BIW"
            Case 5
                strLoc = "K:\CATIA\BUMPER"
            Case 6
                strLoc = "K:\CATIA\COOLING_MODULE"
            Case 7
                strLoc = "K:\CATIA\CRASH_SENSORS"
            Case 8
                strLoc = "K:\CATIA\CRUISE_CONTROL_MOD"
            Case 9
                strLoc = "K:\CATIA\DESIGN_STUDIO"
            Case 10
                strLoc = "K:\CATIA\ENGINE"
            Case 11
                strLoc = "K:\CATIA\EXHAUST"
            Case 12
                strLoc = "K:\CATIA\FACIA"
            Case 13
                strLoc = "K:\CATIA\FASTENERS"
            Case 14
                strLoc = "K:\CATIA\FENDERS"
            Case 15
                strLoc = "K:\CATIA\FUEL"
            Case 16
                strLoc = "K:\CATIA\GROUND_LINE"
            Case 17
                strLoc = "K:\CATIA\HEADLAMPS"
            Case 18
                strLoc = "K:\CATIA\HOOD"
            Case 19
                strLoc = "K:\CATIA\HORN"
            Case 20
                strLoc = "K:\CATIA\HOSES"
            Case 21
                strLoc = "K:\CATIA\INCOMING"
            Case 22
                strLoc = "K:\CATIA\LATCH"
            Case 23
                strLoc = "K:\CATIA\LOADBEAMS"
            Case 24
                strLoc = "K:\CATIA\LWR_CROSSMEMBER"
            Case 25
                strLoc = "K:\CATIA\OIL_RES"
            Case 26
                strLoc = "K:\CATIA\PWR-STRG-ASSY"
            Case 27
                strLoc = "K:\CATIA\SIDE_RAILS"
            Case 28
                strLoc = "K:\CATIA\SSR"
            Case 29
                strLoc = "K:\CATIA\STANDARD_PARTS"
            Case 30
                strLoc = "K:\CATIA\TIPM"
            Case 31
                strLoc = "K:\CATIA\TOC_LINES"
            Case 32
                strLoc = "K:\CATIA\TPMS"
            Case 33
                strLoc = "K:\CATIA\UNITS-SENSORS"
            Case 34
                strLoc = "K:\CATIA\WIRE_HARNESS"
        End Select
    End Sub

Private Function ValidateControls(ByVal ctrl As Control) As Boolean

        Dim c As Control, bReturn As Boolean = True

        For Each c In ctrl.Controls()
            Select Case c.GetType.Name
                Case "TextBox"
                    If (c.Text = "") Then Return False
                Case "ListBox"
                    If (CType(c, ListBox).SelectedIndices.Count = 0) Then Return False
                Case Else
                    If (Not (ValidateControls(c))) Then Return False
            End Select
        Next

        Return True

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If (Not (ValidateControls(Me))) Then
            MsgBox("You must enter all values!")
        Else
            objApp = New Excel.Application
            objApp.Visible = False
            objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=False)

            Dim newRow As Integer

            newRow = objBook.ActiveSheet.UsedRange.Rows.Count + 1

            With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .Weight = Excel.XlBorderWeight.xlMedium
                .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
            End With
            With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .Weight = Excel.XlBorderWeight.xlThin
                .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
            End With
            With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .Weight = Excel.XlBorderWeight.xlThin
                .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
            End With
            With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .Weight = Excel.XlBorderWeight.xlMedium
                .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
            End With
            With objBook.Sheets("RECEIVED FILES").Rows(newRow).borders(Excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .Weight = Excel.XlBorderWeight.xlMedium
                .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
            End With
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 1) = DateTimePicker1.Value
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 1).NumberFormat = "mm/dd/yyyy h:mm"
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 1).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 5) = strMethod
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 5).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 6) = TextBox1.Text
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 6).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 7) = TextBox2.Text
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 7).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 8) = strYourName
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 8).horizontalalignment = Excel.XlHAlign.xlHAlignCenter
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 9) = TextBox3.Text
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 9).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 10) = strLoc
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 10).font.colorindex = 5
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 10).font.underline = True
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 10).horizontalalignment = Excel.XlHAlign.xlHAlignLeft
            objBook.Sheets("RECEIVED FILES").Cells(newRow, 11) = TextBox4.Text

            objBook.Save()
            objBook.Close()
            objApp.Quit()
            Dim p() As Process = Process.GetProcessesByName("EXCEL")

            'kills all processes older than 5 seconds
            Dim i As Integer
            For i = 0 To p.Length - 1
                If ((p(i).StartTime.AddSeconds(5) < DateTime.Now)) Then
                    p(i).Kill()
                End If
            Next
            Dim response As MsgBoxResult
            If MsgBox("Do you want to view the document?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                objApp = New Excel.Application
                objApp.Visible = True
                objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
            Else
                End
            End If
        End If
    End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        objApp = New Excel.Application
        objApp.Visible = True
        objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim p() As Process = Process.GetProcessesByName("EXCEL")

        'kills all processes older than 5 seconds
        Dim i As Integer
        For i = 0 To p.Length - 1
            If ((p(i).StartTime.AddSeconds(5) < DateTime.Now)) Then
                p(i).Kill()
            End If
        Next
        End
    End Sub
End Class
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Make the most of your online learning experience.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

873 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