VBA Userform

[JoyMaree]

I have created a userform where I have code so when the data is entered the userform clears and comes back up so the next lot of data can be entered. However sometimes only part of the Userform reappears.

Once the data is copied across which work fine, then my code is:

'Clear Userform
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl

End Sub

I was unloading the form but still had the same problem.

Below is a copy of the form when the error occurs.
This is all that comes up. There are more columns to the right and below as well as Command Buttons.

Any advice would be appreciated.
Tks

 

[mikerickson]

I wonder what you mean by "the userform clears and comes back". That code will clear the userform, but it shouldn't flicker or become invisible.

Could you post the code that writes to the sheet. And how is this clear code called?

 

[JoyMaree]

Hi mikerickson,

The full code is:

I have also attached a photo of how it should look.

'1. Save & Continue Button - RIS Plant

Private Sub CommandButton1_Click()



Dim i As Integer

Dim j As Integer

Dim k As Integer

Dim g As Integer

Dim e As Integer

Dim h As Integer

Dim Lastrow As Long

Dim d As Date

Dim ctl As MSForms.Control



'Select Plant Time Sheet

Sheets("Plant Time").Select



Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



'Establish data line

linea = WorksheetFunction.CountA(Range(Cells(1, 2), Cells(10000, 2))) + 2



'Copy information from form to spreadsheet



d = Format(DateDay.Value & "/" & DateMth.Value & "/" & DateYr.Value, "dd/mm/yyyy")

Cells(linea + 1, 2) = DateValue(d) 'Date

Cells(linea + 1, 3) = From1.Value 'Time Start

Cells(linea + 1, 4) = To1.Value 'Time Finish

Cells(linea + 1, 5) = Labour1.Value 'Labour Quantity

Cells(linea + 1, 10) = Product1.Value 'Product Code

Cells(linea + 1, 12) = Code1.Value 'Activity Code

Cells(linea + 1, 14) = Tonnes1.Value 'Tonnes Produced

Cells(linea + 1, 20) = Gas1.Value 'Gas Reading

Cells(linea + 1, 24) = Elect1.Value 'Electricity Reading

Cells(linea + 1, 28) = Comment1.Value 'Comments



For i = 2 To 12



If Me.Controls("To" & i).Value = "" Then GoTo EndLines:

Cells(linea + i, 2) = DateValue(d) 'Date

Cells(linea + i, 3) = Me.Controls("From" & i).Value 'Time Start

Cells(linea + i, 4) = Me.Controls("To" & i).Value 'Time Finish

Cells(linea + i, 5) = Me.Controls("Labour" & i).Value 'Labour Quantity

Cells(linea + i, 10) = Me.Controls("Product" & i).Value 'Product Code

Cells(linea + i, 12) = Me.Controls("Code" & i).Value 'Activity Code

Cells(linea + i, 14) = Me.Controls("Tonnes" & i).Value 'Tonnes Produced

Cells(linea + i, 20) = Me.Controls("Gas" & i).Value 'Gas Reading

Cells(linea + i, 24) = Me.Controls("Elect" & i).Value 'Electricity Reading

Cells(linea + i, 28) = Me.Controls("Comment" & i).Value 'Comments



Next



EndLines:



'Copy formulas down



Lastrow = Range("b" & Rows.Count).End(xlUp).Row

Range("f5:i" & Lastrow).FillDown

Range("k5:k" & Lastrow).FillDown

Range("m5:m" & Lastrow).FillDown

Range("o5:s" & Lastrow).FillDown

Range("u5:w" & Lastrow).FillDown

Range("y5:aa" & Lastrow).FillDown



'Select Yard Bags Sheet

Sheets("Plant Silos").Select



'Establish data line

linea2 = WorksheetFunction.CountA(Range(Cells(1, 2), Cells(10000, 2))) + 2



'Copy information from form to spreadsheet

d = Format(DateDay.Value & "/" & DateMth.Value & "/" & DateYr.Value, "dd/mm/yyyy")

Cells(linea2 + 1, 2) = DateValue(d) 'Date

Cells(linea2 + 1, 3) = SiloProd1.Value

Cells(linea2 + 1, 4) = Silo1.Value

Cells(linea2 + 1, 5) = SiloProd2.Value

Cells(linea2 + 1, 6) = Silo2.Value

Cells(linea2 + 1, 7) = SiloProd3.Value

Cells(linea2 + 1, 8) = Silo3.Value

Cells(linea2 + 1, 9) = SiloProd4.Value

Cells(linea2 + 1, 10) = Silo4.Value

Cells(linea2 + 1, 11) = SiloProd5.Value

Cells(linea2 + 1, 12) = Silo5.Value

Cells(linea2 + 1, 13) = SiloProd6.Value

Cells(linea2 + 1, 14) = Silo6.Value



'Save workbook

ActiveWorkbook.Save



'Select Menu Sheet

Sheets("Menu").Select



DateDay.Value = ""



For i = 1 To 12



Me.Controls("From" & i).Value = ""

Me.Controls("To" & i).Value = ""

Me.Controls("Labour" & i).Value = ""

Me.Controls("Product" & i).Value = ""

Me.Controls("Code" & i).Value = ""

Me.Controls("Gas" & i).Value = ""

Me.Controls("Elect" & i).Value = ""

Me.Controls("Tonnes" & i).Value = ""

Me.Controls("Comment" & i).Value = ""



Next



For h = 1 To 6



Me.Controls("Silo" & h).Value = ""



Next



Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

ActiveSheet.DisplayPageBreaks = True



'Clear Userform

For Each ctl In Me.Controls

Select Case TypeName(ctl)

Case "TextBox"

ctl.Text = ""

Case "CheckBox", "OptionButton", "ToggleButton"

ctl.Value = False

Case "ComboBox", "ListBox"

ctl.ListIndex = -1

End Select

Next ctl



End Sub