none
VB CODE IN EXCEL RRS feed

  • Question

  • HI

    I write this code on VB EXCEL 2007.

    In one excel is running in other i got the error :object requied

    Const FIRSTROW = (5)
    Public nameusr As String
    Private Sub CommandButton1_Click()
    Dim inx As Integer
    Dim val
    Dim num_lines As Integer
    Dim row As Integer
    Dim str As String
    Dim num_of_values
    Dim err As String
    Dim ret
    nameusr = ""
    UserForm1.Show (1)
    
    If nameusr = "" Then
        Exit Sub
    End If
    inx = 1
    While (Sheet1.Cells(FIRSTROW, inx + 1) <> "")
        inx = inx + 1
    Wend
    num_lines = inx - 1
    
    If num_lines = 0 Then
        MsgBox "NO PARAMETERS WERE FOUND"
        Exit Sub
    End If
    
    
    
    
    For inx = 1 To num_lines
        
        If Sheet1.Cells(FIRSTROW + 1, inx + 1) = "" Then
            err = "You have error in column" & inx
            MsgBox err
            Exit Sub
        End If
        If Sheet1.Cells(FIRSTROW + 2, inx + 1) = "" Then
            err = "You have error in column" & inx
            MsgBox err
            Exit Sub
        End If
        If Sheet1.Cells(FIRSTROW + 3, inx + 1) = "" Then
            err = "You have error in column" & inx
            MsgBox err
            Exit Sub
        End If
    Next inx
    
    CommonDialog1.InitDir = ThisWorkbook.Path
    CommonDialog1.Filename = nameusr
    
    'CommonDialog1.CancelError = True
    
    On Error GoTo 100
     CommonDialog1.ShowSave
    
    
    
    If nameusr <> CommonDialog1.FileTitle Then
        MsgBox "File name is not valid", , "ERROR"
        Exit Sub
    End If
    
    
    If CommonDialog1.Filename = "" Then
        MsgBox "File was not saved", , "ERROR"
        Exit Sub
    End If
    
    Open CommonDialog1.Filename For Output As 1
    str = num_lines
    Print #1, str
    For inx = 1 To num_lines
        row = FIRSTROW + 3
        num_of_values = 0
        While (Sheet1.Cells(row, inx + 1) <> "")
            
            num_of_values = num_of_values + 1
            row = row + 1
        Wend
        row = FIRSTROW
        str = ""
        While (Sheet1.Cells(row, inx + 1) <> "")
            str = str & Sheet1.Cells(row, inx + 1) & " "
            row = row + 1
            If row = (FIRSTROW + 3) Then
                str = str & num_of_values & " "
            End If
        Wend
        Print #1, str
    
    
    
    Next inx
    Close #1
    
    100: Exit Sub
    End Sub
    
    
    Private Sub CommandButton1_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)
    
    End Sub
    
    Private Sub CommonDialog1_GotFocus()
    
    End Sub
    
    Private Sub CommonDialog1_LostFocus()
    
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    End Sub
    

    Wednesday, April 1, 2020 6:24 AM