Hi alansidman
Thank you so much! Your explanation is correct and I understand it well. However, I keep getting these errors to some of those date/time.
Does this mean I can't change the date/time for those in errors?
Thank you so much!
Posts by mrbuttons
-
-
My apologies. Here you go! Thank you so much!
-
Hi there,
I have a data that shows date/time data. Currently, I couldn't transform the date/time format into a specific one due to unknown reasons.
As you can see above, some of the date/time are in different formats. Whenever I tried to change the date/time format through TEXT Formula or through cells formatting, it doesn't seem to work well.
The date/time format should be in "MM/DD/YYYY" which only works on certain cells.
Are there any ways I can further change the date/time format here? Thank you very much! -
Hi rory ,
thank you! it works! -
Hi there,
I have a file that I would need to group the time into a specific shift. For example,
6.45am to 3.15pm falls under MORNING3.15pm to 11.45pm falls under EVENING
11.45pm to 6.45am falls under NIGHT
I managed to separate the time from the existing datetime column. However, I'm stuck with the grouping using formulas.
Is it possible to group the time into specific shifts using formulas? Thank you! -
Hi there,
Thanks! The subtotal function works! -
Hello everyone,
I have a data collection file with some calculation created. I would like to get my calculations (Column O) to work when I filter the table. How can I get the calculations to work when I filter the table?
Attached here is the file for your references. Appreciate the help and support from you guys here! Thank you so much! -
Hi Roy,
Thanks! This works perfectly!
However, how can I automate it to the entire row of the column? Assuming if there are few rows added, will the formula work for subsequent rows? -
Hi there,
Apologize for the error and confusion. Here's the updated file for you to view.
Thanks! -
-
Hi there,
Here's the attached file.
Thanks! -
Hi everyone,
I would like to achieve a COUNTIFS Function on 3 columns as per the attachment. If the criteria is NO, then it counts as 1 per row.
How can I achieve this in Excel? Currently I'm using COUNTIFS Function tho it's not really working as it is.
Thanks! -
Thanks royUK. I have enabled password protection for the worksheet.
-
Just to add on, here's the full code I designed for the form.
Code- Option Explicit
- Private Sub UserForm_Initialize()
- With Me
- .StartUpPosition = 1
- .Width = Application.Width * 0.5
- .Height = Application.Height * 0.7
- .Left = Application.Left + (Application.Width * 0.5) \ 1
- .Top = Application.Top + (Application.Height * 0.5) \ 1
- End With
- 'fill date drop down box - 1 to 31
- With cmbdate
- .AddItem "1"
- .AddItem "2"
- .AddItem "3"
- .AddItem "4"
- .AddItem "5"
- .AddItem "6"
- .AddItem "7"
- .AddItem "8"
- .AddItem "9"
- .AddItem "10"
- .AddItem "11"
- .AddItem "12"
- .AddItem "13"
- .AddItem "14"
- .AddItem "15"
- .AddItem "16"
- .AddItem "17"
- .AddItem "18"
- .AddItem "19"
- .AddItem "20"
- .AddItem "21"
- .AddItem "22"
- .AddItem "23"
- .AddItem "24"
- .AddItem "25"
- .AddItem "26"
- .AddItem "27"
- .AddItem "28"
- .AddItem "29"
- .AddItem "30"
- .AddItem "31"
- End With
- 'Fill Month Drop Down box - Takes Jan to Dec
- With cmbmonth
- .AddItem "JAN"
- .AddItem "FEB"
- .AddItem "MAR"
- .AddItem "APR"
- .AddItem "MAY"
- .AddItem "JUN"
- .AddItem "JUL"
- .AddItem "AUG"
- .AddItem "SEP"
- .AddItem "OCT"
- .AddItem "NOV"
- .AddItem "DEC"
- End With
- 'Fill Year Drop Down box - Takes 1980 to 2014
- With cmbyear
- .AddItem "2020"
- .AddItem "2021"
- .AddItem "2022"
- .AddItem "2023"
- .AddItem "2024"
- .AddItem "2025"
- .AddItem "2026"
- .AddItem "2027"
- .AddItem "2028"
- .AddItem "2029"
- .AddItem "2030"
- .AddItem "2031"
- .AddItem "2032"
- .AddItem "2033"
- .AddItem "2034"
- .AddItem "2035"
- .AddItem "2036"
- .AddItem "2037"
- .AddItem "2038"
- .AddItem "2039"
- .AddItem "2040"
- End With
- 'Fill chart with a list
- With cmbchart
- .AddItem "Location Chart"
- .AddItem "Spread Chart"
- .AddItem "Others"
- End With
- 'Fill type of trigger
- With cmbtrigger
- .AddItem "Fail Alert Limit UCL"
- .AddItem "Fail Control Limit UCL"
- .AddItem "Fail Spec Limit"
- End With
- With txtdateac
- txtdateac.Text = Format(Now(), "DD/MMM/YYYY/HH/NN/AM/PM")
- End With
- End Sub
- Private Sub btncancel_Click()
- Unload Me
- End Sub
- Private Sub btnsubmit_Click()
- 'Copy input values to sheet.
- Dim irow As Long
- 'Determine empty row
- 'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
- With ActiveSheet
- irow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
- .Cells(irow, 1).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
- .Cells(irow, 2).Value = Me.txtbatch.Value
- .Cells(irow, 3).Value = Me.cmbchart.Value
- .Cells(irow, 4).Value = Me.cmbtrigger.Value
- .Cells(irow, 5).Value = Me.txtfail.Value
- .Cells(irow, 6).Value = Me.txtdisreview.Value
- .Cells(irow, 7).Value = Me.txtdateac.Value
- .Cells(irow, 8).Value = Me.txtempid.Value
- End With
- 'Clear input controls.
- Me.cmbdate.Value = ""
- Me.cmbmonth.Value = ""
- Me.cmbyear.Value = ""
- Me.txtbatch.Value = ""
- Me.cmbchart.Value = ""
- Me.cmbtrigger.Value = ""
- Me.txtfail.Value = ""
- Me.txtdisreview.Value = ""
- Me.txtempid.Value = ""
- End Sub
-
Can we actually make a textbox data from a userform not editable when it is transferred to a column? I have a textbox that collects current date and time when it's submitted. When submitting the form, user will not be able to write or select any date as it comes automatically. (Refer photo below).
However, user can change the date and time after it has been submitted. The only way I can think of is to lock the cells for editing.
Are there any options besides locking the column? Thanks for your support!
-
Hey Roy!
Thank you so much! It worked now! -
Hello everyone,
I made a simple userform for the purpose of collecting data. I managed to get all buttons working except the submit button. I'm not sure what's wrong but I'm guessing it got to do with the "values" attribute? My code is as per below.Code- Private Sub qualform_Initialize()
- With Me
- .StartUpPosition = 1
- .Width = Application.Width * 10
- .Height = Application.Height * 10
- .Left = Application.Left + (Application.Width * 10) \ 1
- .Top = Application.Top + (Application.Height * 10) \ 1
- End With
- empid.Clear
- disreview.Clear
- daterec.Clear
- batch.Clear
- cmbdate.Clear
- cmbmonth.Clear
- cmbyear.Clear
- 'Fill Date Drop Down box - Takes 1 to 31
- With cmbdate
- .AddItem "1"
- .AddItem "2"
- .AddItem "3"
- .AddItem "4"
- .AddItem "5"
- .AddItem "6"
- .AddItem "7"
- .AddItem "8"
- .AddItem "9"
- .AddItem "10"
- .AddItem "11"
- .AddItem "12"
- .AddItem "13"
- .AddItem "14"
- .AddItem "15"
- .AddItem "16"
- .AddItem "17"
- .AddItem "18"
- .AddItem "19"
- .AddItem "20"
- .AddItem "21"
- .AddItem "22"
- .AddItem "23"
- .AddItem "24"
- .AddItem "25"
- .AddItem "26"
- .AddItem "27"
- .AddItem "28"
- .AddItem "29"
- .AddItem "30"
- .AddItem "31"
- End With
- 'Fill Month Drop Down box - Takes Jan to Dec
- With cmbmonth
- .AddItem "JAN"
- .AddItem "FEB"
- .AddItem "MAR"
- .AddItem "APR"
- .AddItem "MAY"
- .AddItem "JUN"
- .AddItem "JUL"
- .AddItem "AUG"
- .AddItem "SEP"
- .AddItem "OCT"
- .AddItem "NOV"
- .AddItem "DEC"
- End With
- 'Fill Year Drop Down box - Takes 1980 to 2014
- With cmbyear
- .AddItem "2020"
- .AddItem "2021"
- .AddItem "2022"
- .AddItem "2023"
- .AddItem "2024"
- .AddItem "2025"
- .AddItem "2026"
- .AddItem "2027"
- .AddItem "2028"
- .AddItem "2029"
- .AddItem "2030"
- .AddItem "2031"
- .AddItem "2032"
- .AddItem "2033"
- .AddItem "2034"
- .AddItem "2035"
- .AddItem "2036"
- .AddItem "2037"
- .AddItem "2038"
- .AddItem "2039"
- .AddItem "2040"
- End With
- End Sub
- Private Sub btncancel_Click()
- Unload Me
- End Sub
- Private Sub btnsubmit_Click()
- 'Copy input values to sheet.
- Dim lRow As Long
- 'Make Sheet1 Active
- Sheet1.Activate
- 'Determine empty row
- emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
- With ws
- .Cells(lRow, 1).Value = Me.empid.Value
- .Cells(lRow, 2).Value = Me.disreview.Value
- .Cells(lRow, 3).Value = Me.daterec.Value
- .Cells(lRow, 4).Value = Me.batch.Value
- .Cells(IRow, 5).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
- End With
- 'Clear input controls.
- Me.empid.Value = ""
- Me.disreview.Value = ""
- Me.daterec.Value = ""
- Me.batch.Value = ""
- Me.dateac.Value = ""
- End Sub
Thank you for your support!
-
Hello everyone,
I have a worksheet containing a set of information and data. In the second worksheet (Worksheet B), I have summarized version of all information from worksheet A which I incorporated the COUNTIF Function to get the simplified version of the data. Each cells in Worksheet B is using the COUNTIF function since they are different to each other.
I have attached the file below. It would be good if I can get a starting line of code of the first cell. I will pickup the rest of the codes and will continue for the rest of it. Thank you so much everyone! -
-