1. J

    How to re-arrange a large number of rows into their respective bin ranges?

    Hi guys, I'm given a data set on property type as a school assignment and i'm having great difficulty in creating graphs out of numerically-assigned categorical variables. For example, my variable on gender of property buyer has 0 = male, 1 = female 2 = joint purchase. Is there a way for me to...
  2. D

    Calculating the different components of my IRR

    I have determined an IRR of a commercial property from its cash flows after a sale in year 10. How do i go about figuring out what portion of my IRR comes from cash flows and what portion comes from the sale of the property?
  3. H

    Loop through properties and values for controls on userform.

    Hi I am able to loop through all Userforms and all Controls but I need to loop through all all of the properties of each control. Property and Properties seem to be data types according to Intellisense in the VBA Editor but are not listed in the VBA data types list. Are these what I should...
  4. sharky12345

    Populate Combobox with Dynamic Range problem

    I'm using this to populate a series of Comboboxes on a Userform from a dynamic named range; Me.Controls("ComboStaff" & i).List = ws.Range("NewAvailableStaff").Value It works fine until there is only 1 item left in the range and then I get an error, Is there a way I can avoid this?
  5. D

    Win 7 Access 2007 property sheet not enabled

    In design mode for a form I sometimes will not be able to see properties for text box or form etc. To enable the property sheet in the immediate window I enter CommandBars("Property Sheet").Enabled = True Is there any way to always have the property sheet enabled? Or to do it a start up time...
  6. C

    ComboBox ERROR when trying to clear after INDEX/MATCH lookup...

    Hi guys, I searched the forum and found some code which I was able to use in my form to create a lookup to populate other TextBox within my Form. The code works fine when matching and looking up the values for the other cells, but I am not able to delete the value/enter my own random property...
  7. T

    Class instead of array

    On my worksheet, in column A, I have 5 rows of data and a heading in cell A1. Say: Name a a b c d This is my code without using classes: Option Explicit Sub NoClass() Dim MyArray(1 To 6, 1 To 1) As Variant Dim i As Integer For i = 2 To 5 If MyArray(i, 1) <> MyArray(i + 1, 1)...
  8. M

    VBA help

    I keep getting out of memory error with this VBA script. and It highlights the following line : ReDim V(1 To WorksheetFunction.Combin(UBound(Vals), 7), 1 To 7) Can someone help me please to fix this error? thanks. This is the regular module: Option Explicit 'Set Reference to Microsoft...
  9. C

    ActiveX control properties; differences between design time and run time

    I understand that there is 2 lists of control properties. The ones at design time (static) can be changed manually through the VBA Property Window, and the ones that can be changed at run time (dynamic). Is it possible to programmatically change the static ones so I can see the changes in the...
  10. T

    Let Property passing argument

    I seem to be confused with regard to ByRef and ByVal. Here, regardless of ByRef or ByVal, the result in the immediate window is 100. I though ByVal DOESN'T change things. Option Explicit Dim pabc As Long Sub Test() abc = 10 Debug.Print pabc End Sub Property Let...
  11. T

    Function v Get Property

    What's the difference between a function and the Get Property? Both TestFunction and TestProperty generate the same results. Option Explicit Sub TestFunction() Dim d As Long d = MyFunction(1, 2, 3) End Sub Sub TestProperty() Dim d As Long d = MyProperty(1, 2...
  12. C

    Best method for changing control properties at design time

    Hi I have set up 20+ user forms with approximately 50 controls in each one. When designing the forms, it seemed inefficient to give a unique meaningful name to each and every control, so they are currently named randomnly, i.e CheckBox1 could be beside CheckBox101 but still related to the...
  13. T

    Class on a class

    I have a variable, Var1, which is of type Class2. Standard Module: Option Explicit Sub Test() Dim a As Class1 Set a = New Class1 a.Var1.Var2 = 7678 End Sub Class1 Option Explicit Private pVar1 As New Class2 Public Property Get Var1() As Class2 Set...
  14. O

    VBA - Conditional Formatting - Modify formula1

    Hello all. I can get the modify sintaxe correctly to AppliesToRange as below but I can't get to ModifyFormula1. Sub CFTest ActiveCell.FormatConditions(1).ModifyAppliesToRange Range("$L$2:$L$115") 'this works properly ActiveCell.FormatConditions(1).ModifyFormula1 "=$I2=1" 'this...
  15. R

    Getting at both values of a Combobox using the name property

    I have a class file that stores each control for a series of userform comboboxes and textboxes Class module: materialField Option Explicit Private fieldName As String Private fieldValue As Variant Private fieldValue2 As String Property Get Name() As String Name = fieldName End...
  16. B

    listbox form control scrollbar question

    I want the scrollbar at the top, and I've seen solutions to use Sheets("Main").ListBoxes("List Box 1").scrolltop but excell says it's not a property or method. Does this only work with ActiveX? Can it be done with a simple form control? What about .topindex which the documentation from...
  17. M

    VBA data model to access calculated columns?

    I'm unable to find a way in VBA to identify calculated columns in the data model. I can enumerate the columns Dim objMTables As ModelTables Dim objMCols As ModelTableColumns Dim objMCol As ModelTableColumn Set objMTables = ActiveWorkbook.Model.ModelTables For Each...
  18. S

    Excel 2016 UserForm Listbox - Can listbox horizontal scrollbar be disabled?

    Hi, I don't need the horizontal scrollbar on my userform listbox, is there a property that disables it? Thanks!
  19. T

    VBA problem with FullSeriesCollection object, how to get "Values" Property returned?

    I added a series to a chart and recorded as a Macro. The following VBA was recorded: Sub Macro2() ' ' Macro2 Macro ' ActiveChart.ChartArea.Select ActiveChart.PlotArea.Select Application.CutCopyMode = False ActiveChart.SeriesCollection.NewSeries...
  20. A

    Named Range in Cells Property

    I named the column W as S_Date, and tired to use it in my VBA code. But it doesn't work. I know I can use the named range in Range Property, but it doesn't seem to work in Cells. How to solve this? Thank you row1 = WS1.Cells(WS1.Rows.Count, Sheets("Template").Columns("<wbr>S_date")).End(xlUp).Row

Some videos you may like

This Week's Hot Topics

  • SUMPRODUCT active link formula
    Hi guys i have sumproduct formula for counting two range of number, i want count active cells of formula that linked to another sheet...
  • Block certain cells in condition met in cell A
    Hi there, trying to figure out step by step how to build macros and learn more. Now given that my other code was a mess, I figured I would...
    Hi, Below formula works well, =(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O28='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P28='PRICE...
  • Match data from 3 columns to return data from the correct 4th column
    Hi there! I'm trying to have a cell auto-populate the data in a cell based on the data entered in 3 other cells. I've pasted a copy of the...
  • VLookup
    Hi everyone, I need to find the value from one sheet to another. So in Sheet A Field N5 I have a value (Spark) I want to find Spark on the Sheet...
  • Defining a range
    Private Sub Worksheet_Calculate() Dim Xrg As Range Set Xrg = Range("K1") If Not Intersect(Xrg, Range("K1")) Is Nothing Then MsgBox...

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