VBA Userform

JoyMaree

New Member
Joined
Sep 12, 2018
Messages
16
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.

Capture.JPG

Any advice would be appreciated.
Tks
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,909
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

New Member
Joined
Sep 12, 2018
Messages
16
Hi mikerickson,

The full code is:

I have also attached a photo of how it should look.
Capture.JPG
'1. Save & Continue Button - RIS Plant

VBA Code:
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
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,122,582
Messages
5,597,020
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top