Posts by ivanian

Hello Ozgrid!

How are you all today?

So we have this rows of data that we are extracting from a system and equally distributing to a team of 8 to work on for the day. The number of rows varies each day as well as the sequence of who takes the first set, second set, third set of data and so on. I also have to take into consideration the off days of the team members and if someone is to call-in sick.

The process goes like this. [TABLE="border: 1, cellpadding: 1, cellspacing: 0"]

[TD="width: 64"]Day1[/TD]
[TD="width: 64"]Day2[/TD]
[TD="width: 64"]Day3[/TD]
[TD="width: 64"]Day4[/TD]
[TD="width: 64"]Day5[/TD]

Member1

Member8

Member7

Member6

Member5

Member2

Member1

Member8 ( off day )

Member7

Member6

Member3

Member2

Member1

Member8

Member7

Member4

Member3

Member2

Member1

Member8

Member5

Member4

Member3

Member2

Member1

Member6

Member5

Member4

Member3

Member2

Member7

Member6

Member5

Member4

Member3

Member8

Member7

Member6

Member5

Member4

[/TABLE]

Day1 = 1000 rows of data (excluding header) → (1000 - (1000 Mod / 8 = 125 each
Member1 gets the first 1st to 125th row, Member2 gets 126th to 250th row ........... Member 8th gets the last set of data that is 876th to 1000th.

Day2 = 800 rows of data (excluding header) → (800 - (800 Mod / 8 = 100 each
Now Member8 gets the first set, Member1 gets the second set ......... Member7 gets the last set.

Day3 = 1200 rows of data (excluding header) → (1200 - (1200 Mod 7) / 7 = 171 each and the remaining 3 will be added to the last set. So Member6 gets a total of 174.
Member 7 gets the first set, Member1 gets the 2nd set ( since Member8 is not working this day ) .......... Member6 gets the last set.

and the cycle goes on ( as shown in the table above ). Basically, the set of data is being rotated each day.

Any idea how to implement the above in simple but efficient and bulletproof way?

I'm thinking of a UserForm of Checkboxes with the names of each member. Checkbox of the respective member will be ticked if it's their working day by default. From there, the program will count the how many members are present and will divide the data accordingly. I know I said simple but the checkbox idea came to mind because of the OpenFile dialog that will prompt the user for the raw data that's extracted from the system which will then remove unnecessary data columns and row before distributing it to the team. That being said, I'm having trouble thinking of a way to implement the idea. Any help and suggestion will be much appreciated.

PS: I hope what I said above makes sense
[ATTACH=JSON]{"alt":"Click image for larger version Name:\tCapture.PNG Views:\t1 Size:\t15.0 KB ID:\t1200703","data-align":"none","data-attachmentid":"1200703","data-size":"full","title":"Sample Form"}[/ATTACH]

Hi Mumps,

Thanks for your reply. I ended up using AdvancedFilter with a used of Array for the criteria. Assigned values then transpose somewhere in the worksheet then just delete if after use.

PS: Sorry for the late reply, didn't have free time to code these past few days.

Hello guys!

I've been trying to work on a macro that will extract columns and rows of data with a given criteria from a master file and so far I have only managed to get the columns that I need by looping through the header row and copying the entire column and appending it in a table to another sheet. As for the rows, I managed to copy to certain rows with advancefilter copy but with only one criteria. What I need to to do is extract rows that meets multiple criteria. Say for example, in Col1, I need the row that has reqval1 or reqval2 that is not xx1 in either col2 or 3. So for the sample date below, the only rows that I need are, row2, row4 and row6. Can anyone tell me the most efficient way to do what I need please? Hopefully, what I;m trying to do makes sense. Thanks all!
[TABLE="align: left, border: 0, cellpadding: 1, width: 500"]

Col1

Col2

Col3

Col4

Col5

ReqVal1

RndVal

xx1

RndVal

RndVal

ReqVal2

RndVal

RndVal

RndVal

RndVal

RndVal

xx1

RndVal

RndVal

RndVal

RndVal

xx2

RndVal

RndVal

RndVal

ReqVal1

RndVal

RndVal

RndVal

RndVal

ReqVal2

RndVal

xx1

RndVal

RndVal

ReqVal1

RndVal

RndVal

RndVal

RndVal

[/TABLE]

Hello,

I'm trying to use Index Match as a VBA code while using a table as a my lookup array. I have no problem using it in excel formula but I can't my way around when converting to VBA code.

this is the code that I'm trying to use but I'm getting an 'Object doesn't support' error

Set myTbl = ThisWorkbook.Worksheets(ptSource).ListObjects(ptSource)
    Set vouchers = ThisWorkbook.Worksheets(1).ListColumn(1)
    Set invoiceNo = ThisWorkbook.Worksheets(1).ListColumn(2)
    
    With Application.WorksheetFunction
         MsgBox .Index(vouchers, .Match(myLookUp, invoiceNo, 0))
    End With

I'm basically using the table columns as my lookup range. I hope that my question and what I'm trying to do makes sense.

Thanks all.

Re: Add same change event to multiple ComboBoxes

There are 2 Groups of comboboxes in the form. First group is not inside a frame and populated with a namerange. 2nd Group is in a frame and populated with a different namerange. I included the Worksheet in the attachment. Hopefully, there's a way for it. Thanks again

Re: Add same change event to multiple ComboBoxes

Hi Pike,

You're a life-saver . Didn't think of a procedure like that. By the way, is there anyway to apply the same approach for the comboboxes inside a frame?

EDIT: I have attached the original tool that I'm working on for more details.forum.ozgrid.com/index.php?attachment/68937/

Re: Add same change event to multiple ComboBoxes

I'm actually getting a Mismatch type after I implemented it to the original form that I'm working with. Probably because of the other controls like TextBox and ListBox. I tried to removed them and it executed without a problem. I'm looking into putting the other controls inside a Frame as a workaround but it would be nice if I could get it to work without having to resort to Frames.

EDIT: Putting the other controls inside a Frame does not fix the problem. As soon as the I click inside the Frame the 'Mismatch' will trigger.

Re: Add same change event to multiple ComboBoxes

Quote from pike;769761

you picked a doozie to start with

I've been reading books in between. Just trying to squeeze-in whenever work doesn't ask too much of my time.

Again, thanks for the help. I'd like to mark this SOLVED but it seems that there's no option to edit the thread title.

Re: Add same change event to multiple ComboBoxes

Quote from pike;769758

This is the way to go with on enter control class . workbook attached

classes of this level are very hard to grasp as there are lots of very rare methodologies used

Thanks Pike. Yeah, I saw the code earlier and there are lots of methods that I'm not familiar (not surprising though as I'm still new to VBA Excel). Nevertheless, there is never a bad code to learn.

Thank you for this.

Re: Add same change event to multiple ComboBoxes

Quote from pike;769753

change event is triggered when there is a change in the combobox .. there is no change as its empty .. fill the comboxboxes and change the value .. it will trigger
but one of the guns will know why

Actually I tried it too. I added a loop to fill the combobox in UserForm module . But still won't trigger the Function FillList when combobox is changed.

Re: Add same change event to multiple ComboBoxes

Quote from pike;769749

try

http://www.ozgrid.com/forum/sh…96122&p=753149#post753149

I'm trying to see how I should implement my Function there but I can't seem to have a fully grasp of how the code works.

Re: Add same change event to multiple ComboBoxes

Quote from pike;769747

http://www.ozgrid.com/forum/showthread.php?t=154659

where did you find yours?

Thanks Pike. This would a nice resource for me. I found mine here in Ozgrid too. It was originally for TextBoxes and I just modified it a bit and change the controls to ComboBoxes.

But I can't seem to make the Function FillList to run.

Here's the link . http://www.ozgrid.com/forum/showthread.php?t=80631

Re: Add same change event to multiple ComboBoxes

Hello Pike,

Exactly what I needed. Thank you so much. Is there a thread where the code came from? I am hoping that I could study the code so I could understand it well.

And another thing. Do you happen to know what could be the problem with the code below? The change event won't execute. It's a class that I found online thatI modified a bit.

'clsObjHandler' Class

Option Explicit
Private WithEvents cbxCustom1 As MSForms.ComboBox 'Custom ComboBox
 
Public Property Set Control(cbxNew As MSForms.ComboBox)
    Set cbxCustom1 = cbxNew
End Property
 
Private Sub cbxCustom1_Change()
    UserForm1.FillList cbxCustom1
End Sub
Private Sub Class_Terminate()
 'Destroy The Class Object And Free Up Memory
Set cbxCustom1 = Nothing
End Sub

UserForm1 Module

Option Explicit
Dim colCbxs As Collection 'Collection Of Custom ComboBoxes
Private Sub UserForm_Initialize()
    Dim ctlLoop As MSForms.Control
    Dim clsObject As clsObjHandler
     
    Set colCbxs = New Collection
    
    For Each ctlLoop In Me.Controls
        If TypeOf ctlLoop Is MSForms.ComboBox Then
            Set clsObject = New clsObjHandler
            Set clsObject.Control = ctlLoop
             
            colCbxs.Add clsObject
        End If
    Next ctlLoop
End Sub
Private Sub UserForm_Terminate()
     'Destroy The Collection To Free Memory
    Set colCbxs = Nothing
End Sub

I hope you don't mind. Just trying to learn as much as I can.

Thanks in advance.

Following my previous post, I have managed to make a Function that populates multiple comboboxes from a same source and remove items that already exist in other ComboBoxes. Now my problem is I have to call the Function to each comboboxes which makes it redundant. I'm looking into firing the same change event to multiple comboboxes at once.

This is what my code looks like so far.

Private Sub UserForm_Initialize()
    Dim ctl As Control
    For Each ctl In Controls
       FillList ctl
    Next
End Sub
Private Sub ComboBox1_Change()
    Dim ctl As Control
    For Each ctl In Controls
       FillList ctl
    Next
End Sub
Private Sub ComboBox2_Change()
    Dim ctl As Control
    For Each ctl In Controls
       FillList ctl
    Next
End Sub
Private Sub ComboBox3_Change()
    Dim ctl As Control
    For Each ctl In Controls
       FillList ctl
    Next
End Sub ' And so on 


' #### Function to populate list without repeating values
'
Public Function FillList(cbx As ComboBox)
    Dim myRng As Range, ctl As Control
    Set myRng = ThisWorkbook.Names("petSkill").RefersToRange
    Dim e, v
    With myRng
        v = .Value
    End With
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
        If Not .exists(e) And e <> ComboBox1.Value _
            And e <> ComboBox2.Value And e <> ComboBox3.Value _
            And e <> ComboBox4.Value And e <> ComboBox5.Value _
            And e <> ComboBox6.Value And e <> ComboBox7.Value _
            And e <> ComboBox8.Value And e <> ComboBox9.Value _
            And e <> ComboBox10.Value And e <> ComboBox11.Value _
            And e <> ComboBox12.Value Then .Add e, Nothing
        Next e
        If .Count Then
        cbx.List = Application.Transpose(.keys)
        End If
    End With
End Function

I've seen some approach using Collections but I can't seem to figure it out as to how should I apply the same method that suits my needs.

Any help is greatly appreciated.

Thanks and regards,

Re: Userfrom Controls resizing on its own.

Quote from pike;769582

oh update button .... it a pain with just a photo .. move the update to the multipage and increase the fixedtabheight property of the multipage tabs to cover the gap

Hello Pike,

Thanks for your reply, sorry I forgot to mention that the first photo was opened from a unit with a lower resolution than the second photo which was opened with a higher resolution unit.

Nevertheless, I will implement the property that you mentioned above.

Thanks.

Hello all,

Just want to ask if there's anyway to avoid the userform controls resizing on its own? As you can see in the first picture, the form is displaying the controls the way it should be, but in the second picture. The 'Ring' tab and 'Update' button is overlapping each other.

[ATTACH=CONFIG]68915[/ATTACH]
[ATTACH=CONFIG]68914[/ATTACH]

Hopefully, there's a fix on this. I've tried searching it off the internet and there are articles about ActiveX being quirky when placed in a Worksheet but I haven't seen one inside a userform.

Re: Change label if string contains a ComboBox Value

Hello Skywriter, you really are a life saver. That's exactly what I needed. Thank you so much.

I could've sworn that I tried that too but for some reason the Captions stays blank.

I did a little modification because I realized that I'll be adding more columns in the future and decided to store it in multidimensional array, now I'm back where I started. Could you please take a look which part am I missing? Thanks in advance

Public Sub SetBonus()
    Dim myset As Range,r%, cnt As Range, x%
    
myTbl = Worksheets("DB").ListObjects("tbl_setBonus").DataBodyRange
    Set myset = ThisWorkbook.Names("setBns").RefersToRange


    For Each cnt In myset
        
        Select Case InStr(cbxItem.Value, cnt)
        Case 0
            Label1.Caption = ""
            Label2.Caption = ""
            Label3.Caption = ""
        Case 1
            For r = LBound(myTbl) To UBound(myTbl)
            If myTbl(r, 1) = cnt Then Exit For
            Next r
                Label1.Caption = myTbl(r, 2)
                Label2.Caption = myTbl(r, 4)
                Label3.Caption = myTbl(r, 6)
        End Select
    Next cnt
End Sub

Re: Change label if string contains a ComboBox Value

Hello,

Thanks and worksheet attached.

So I have a label that will depend on the value of ComboBox. If the Value of ComboBox contains a specific string then it'll execute a Match Index from the range that I specified.
I managed to do what I wanted but the problem now is I want to clear the label if the ComboBox does not contain the string that I'm looking for. I tried to add an 'ElseIf' then put the caption to blank but after doing so, if I changed that value of ComboBox it will stay as blank.

Public Sub SetBonus()
    Dim mySet As Range, twoParts As Range, fourParts As Range, sevenParts As Range, cnt As Range
    Set mySet = ThisWorkbook.Names("setBns").RefersToRange
    Set twoParts = ThisWorkbook.Names("twoParts").RefersToRange
    Set fourParts = ThisWorkbook.Names("fourParts").RefersToRange
    Set sevenParts = ThisWorkbook.Names("sevenParts").RefersToRange
                
    For Each cnt In mySet
        If InStr(cbxItem.Value, cnt) <> 0 Then
        With WorksheetFunction
            Label1.Caption = .Index(twoParts, .Match(cnt, mySet, 0))
            Label2.Caption = .Index(fourParts, .Match(cnt, mySet, 0))
            Label3.Caption = .Index(sevenParts, .Match(cnt, mySet, 0))
            End With
        End If
    Next cnt
End Sub

Anyone please?

Thanks in advance,

Re: Run macro only if condition is met.

Have you tried 'Isempty'?

I don't use pivot but you can try something like this.

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("Tabela przestawna2")


If Not IsEmpty(pvt) Then
   RunThemAll
End If