Posts by mrbuttons

mrbuttons · Aug 26th 2020

Hi there,

Not really.

From Column G to J, there are two "No" captured there. Therefore, it should be counted as 2 in the column J there.

mrbuttons · Aug 26th 2020

Hi there,

Here's the attached file.

Thanks!

mrbuttons · Aug 26th 2020

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!

mrbuttons · Mar 2nd 2020

Thanks royUK. I have enabled password protection for the worksheet.

mrbuttons · Feb 27th 2020

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

Display More

mrbuttons · Feb 26th 2020

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).

Code

With txtdateac
    txtdateac.Text = Format(Now(), "DD/MMM/YYYY/HH/NN/AM/PM")
    End With

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!

mrbuttons · Feb 21st 2020

Hey Roy!

Thank you so much! It worked now!

mrbuttons · Feb 20th 2020

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

Display More

Thank you for your support!

mrbuttons · Jan 8th 2020

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!

mrbuttons · Dec 24th 2019

I see. I guess it doesn't revolve around screen updating that much.

mrbuttons · Dec 19th 2019

Hello guys.

Not sure about the screen updating function here. Do I need to set it as true to enable the data to be updated automatically? Thank you!

mrbuttons · Dec 18th 2019

Hello everyone!

I just started learning VBA script writing and Makros. I managed to write a script whereby I merge multiple excel workbooks into one master worksheet. At this point, I have accomplished one part of the task. However, I couldn't proceed to make the master file to auto-update automatically if I add or change a data to the slave files. Below is the code that I have written.

Code

Sub MergeExcelFIle()
Dim wb As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = True
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'Include your path folder here containing all excel files
'Set all objects relevant to the folder and list down all the files needed for the merging
Set dirObj = mergeObj.GetFolder("C:\Users\pathhhhh")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set wb = Workbooks.Open(everyObj)

'Change cell reference to the starting point of your data
'For example, if you begin with cell A2 to merge, start your range from A2
'Change ''A" column on "A65536" to the same column as start point
Range("A2:AZ" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same as above.
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
wb.Close
Next
End Sub

Display More

My code copies multiple excel files into a single master worksheet. I have changed the code to ensure that the code captures data to be copied within a selected range.

Code

'Change cell reference to the starting point of your data
'For example, if you begin with cell A2 to merge, start your range from A2
'Change ''A" column on "A65536" to the same column as start point
Range("A2:AZ" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

Is there any functions that I need to key-in within the code to enable the master file to retrieve data automatically if there are changes made to the slaves file? I have tried PowerQuery as a substitute for this but I don't think that PowerQuery has the function to enable the master file to maintain the same layout as what I want and to input photos as well within the file itself (Correct me if I am wrong).

I am also open to suggestions if there's anything wrong with my code. Let me know if there's anything I can do to improve myself. Thanks everyone!

Posts by mrbuttons

CountIf Multiple Columns per Rows

CountIf Multiple Columns per Rows

CountIf Multiple Columns per Rows

Hi everyone,

Userform-Transferred Data must not be editable

Userform-Transferred Data must not be editable

Userform-Transferred Data must not be editable

Userform - Submit Button not working

Userform - Submit Button not working

Multiple CountIf Functions into a VBA Script

Merge multiple excel books into a single master worksheet. Master sheet to auto update automatically if data is changed in slave books.

Merge multiple excel books into a single master worksheet. Master sheet to auto update automatically if data is changed in slave books.

Merge multiple excel books into a single master worksheet. Master sheet to auto update automatically if data is changed in slave books.