User form don't write if no data is entered

**nods** · 11-06-2010, 09:12 AM

Hi

I have created this control panel in Excel vba and need some help to clean up the code and make it run more efficent.

What I would like to add is an error trap that will produce a warning box advising the user thay haven't selected a week or site also currently the program will write the default values to the workbook. I would like to set it so that if the user enters nothing then nothing (not even a blank space) is written to the work book.
Reason is that the user may only enter data for 1 day but the default data for all days is being written to the workbook which takes up unneccesary space.

Goal - Would like to show an error if WeekDD and SiteDD default values have not been changed and if TextBox1 or TextBox2 default values have not been been changed nothing is to be written to the workbook.

The TextBox default values are 0
WeeDD Default value is week
And SiteDD default value is Site

Code I have so far is:

 Private Sub adjbtn1_Click()

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Friday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox1.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox2.Value

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Saturday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox3.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox4.Value

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Sunday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox5.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox6.Value

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Monday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox7.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox8.Value

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Tuesday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox9.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox10.Value

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Wednesday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox11.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox12.Value

Sheets("Adjustments").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.WeekDD.Value
Sheets("Adjustments").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Adjustments").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = "Thursday"
Sheets("Adjustments").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Me.TextBox13.Value
Sheets("Adjustments").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.TextBox14.Value

Worksheets("Adjustments").Calculate
Worksheets("Overview").Calculate

End Sub

Can anyone offer some guidance on how to achieve this?

I know I am asking lots of questions but I am new to creating forms in VBA. many thanks in advance.

**royUK** · 11-06-2010, 09:58 AM

I would loop through the controls to check the settings, but it's hard to say what needs doing without seeing the form

**nods** · 11-11-2010, 12:37 AM

this was solved with

Private Sub RunBtn_Click()

    Dim vDay        As Variant
    Dim sDay        As String
    Dim iCnt        As Long

    If WeekDD2.Value = "Select Week" Or SiteDD2.Value = "Select Site" Or DeptDD2.Value = "Team/Dept" Then
        MsgBox "Please Select a Site, Week, and Department", vbExclamation, "Error"
        Exit Sub
    End If
            Sheets("Rota").Range("G2").Value = Me.WeekDD2.Value
            Sheets("Rota").Range("C2").Value = Me.SiteDD2.Value
    
    For iCnt = 1 To 30
        
        For Each vDay In Array("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday")
          
         sDay = Left(vDay, 3)
         
            
            If TeamMbr1.Value <> "Team Mbr" And _
               Me.Controls(sDay & "Start" & iCnt).Value <> "Start" And _
               Me.Controls(sDay & "Finish" & iCnt).Value <> "Finish" Then

                RotaOut Array(Me.WeekDD2.Value, _
                              vDay, _
                              Me.SiteDD2.Value, _
                              Me.DeptDD2.Value, _
                              Me.TeamMbr1.Value, _
                              Me.Controls(sDay & "Start" & iCnt).Text, _
                              Me.Controls(sDay & "Finish" & iCnt).Value)
                              
                If vDay = "Friday" Then
                   

             End If
            End If
        
        Next vDay
        
    Next iCnt
Worksheets("Rota Input").Calculate
    Worksheets("Rota Tables").Calculate
    Worksheets("Rota").Calculate
    Worksheets("Overview").Calculate
    Worksheets("Rota").Select
    MsgBox "Saved.", vbInformation

    End Sub