Posts by Carim

Re: Locked combobox

Hi Marti,

You could test ...

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ComboBox1.Locked = False
If OptionButton1.Value = False And OptionButton2.Value = False Then ComboBox1.Locked = True
End Sub

HTH

Re: ComboBox with complicated problem

Glad you could fix your problem ...:smile:

Happy New Year to you ...

Excellente Année 2016 :wink:

Re: ComboBox with complicated problem

Hello again,

Following an excellent remark from cytop, to be on the safe side, you should test:

Private Sub ComboBox4_Change() 
    TextBox23.Value = Val(ComboBox4.Value) * Val(TextBox26.Value) 
    TextBox24.Value = Val(TextBox22.Value) + Val(TextBox23.Value) 
End Sub

HTH

Re: ComboBox with complicated problem

Hello Marti,

A second test ...

Private Sub ComboBox4_Change()
    TextBox23.Value = ComboBox4.Value * CLng(TextBox26.Value)
    TextBox24.Value = CLng(TextBox22.Value) + CLng(TextBox23.Value)
End Sub

HTH

Re: ComboBox with complicated problem

Hi Marti,

You could test the following :

Private Sub ComboBox4_Change()
TextBox23.Value = ComboBox4.Value * TextBox26
TextBox24.Value = TextBox22.Value + TextBox23.Value
End Sub

HTH

Re: Setting colors of cell interior in other sheets

Hi,

In the module ThisWorkbook .... Ten_skoroszyt ... (my Polish is very weak ...) :wink:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x As Long
  For Each Sh In ThisWorkbook.Sheets
    If Sh.name <> "LIST" Then
      If Target.Column <> 1 Then
        x = Application.Match(Target, Sheets("LIST").Range("A1:A100"), 0)
        Target.Interior.Color = Sheets("LIST").Cells(x, 1).Interior.Color
      End If
    End If
  Next Sh
End Sub

Hope this will help

:smile:

Re: Running a macro when a shape is created

Hi,

You could take a look at following site

https://www.add-in-express.com/creating-addins-blog/2012/02/21/excel-shapes-events/

HTH

Re: Macro to move all files including Sub-Folders

Hi,

Would recommend to take a look at Ron's site ... http://www.rondebruin.nl/win/s3/win026.htm

HTH

Re: Count column if NOT a duplicate (Excel 2010)

Hi,

In order to count Unique values, you can test following :

=SUMPRODUCT((K7:K3007<>"")/COUNTIF(K7:K3007,K7:K3007&""))

HTH

Re: Sum from match to last row

Glad this could help you solve your problem ...:smile:

The Offset function requires a starting point A1, then the number of rows, and the number of columns ...

In this case, MATCH("a",A1:A20,0)-1 will generate the number of rows ... to reach the starting row ...

Without this issue of matching the starting row ... your formula would be =SUMPRODUCT((A1:A10="a")*(A1:A10="x")*(B1:B10))

Hope this clarifies ...

Happy Holidays ... to you too ... :smile:

Re: Sum from match to last row

An attempt within a single formula ...

=SUMPRODUCT(((OFFSET(A1,MATCH("a",A1:A20,0)-1,0):A10="a")+(OFFSET(A1,MATCH("a",A1:A20,0)-1,0):A10="x"))*(OFFSET(B1,MATCH("a",A1:A20,0)-1,0):B10))

Obviously "a" has to be replaced by a cell containing "a" or "b" or "c" ... since "x" seems to be fixed ...

Does it produce the expected result ?

Re: Sum from match to last row

A basic UDF could like the following :

Function RobySum(init As String, rng As Range)
Dim x As Long
Dim y As Long
Dim i As Long


x = Application.Match(init, rng, 0)
y = Range("A65536").End(xlUp).Row
  For i = x To y
    If Cells(i, 1).Value = init Or Cells(i, 1).Value = "x" Then
      RobySum = RobySum + Cells(i, 2)
    End If
  Next i
End Function

Hopefully, the result is correct ...

Now, for a non-UDF solution ... do you mind if there is a helper column ?

HTH

Re: Sum from match to last row

Hi,

Sorry I did not realize there was one more condition ... the non matching rows ...

Your life would be easier with a UDF ... :smile:

Are you familiar enough with macros ... or would you like an example ...?

Re: Sum from match to last row

Hi,

You could test following formula :

=SUM(INDIRECT(ADDRESS(MATCH("b",A1:A20,0),2,4)):INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B20))*(B1:B20<>"")))))

HTH

Re: VBA - Unselect Pivot Items based on Range Values

Glad you could fix your problem ...:smile:

Re: VBA - Unselect Pivot Items based on Range Values

From looking at your code ... there is no apparent problem ...

The only recommendation would be : before getting into the loop which will hide or show items ... just make sure to initially have all your items set to Visible ...

HTH

Re: VBA - Unselect Pivot Items based on Range Values

Hi again,

From your comment, I do not fully understand your obstacle ...

When you say "unselecting a specific item" ... how does that differ from setting the item Visible property to False ?

Re: VBA - Unselect Pivot Items based on Range Values

Hi Joe,

To show and hide items, you can have a look at Debra's comprehensive help ...

http://www.contextures.com/xlPivot03.html

HTH

Re: VBA Pop-up box double click contents from another sheet

Hi Anthony,

Most probably in Sheet2, you can use the event Worksheet_BeforeDoubleClick() ...

When you say "return Names" ... do you mean that each and everycell in Sheet1 was given a range name ?

Re: XML error after saving workbook using VBA

Most probably ... this link will help ...

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

HTH