Posts by Macropheliac

Re: Menu Animation

I found the problem, I think. It seems that property can be used only on the CommandBars collection object and not on an individual CommandBar.

Application.CommandBars.MenuAnimationStyle = msoMenuAnimationUnfold

Thanks,
Mac

Hello all!

I am attempting to set the Menu Animation Style of a custom toolbar. After searching the VBA Help files, I tried this:

Sub Set_Style()
Dim iBar As CommandBar


    Set iBar = Application.CommandBars("Program Report Bar")
    iBar.MenuAnimationStyle = msoMenuAnimationUnfold
End Sub

However, it produces the compile error:

Quote

Method or Data member not found

There is no help available for this error. Does anyone have an idea?

As always, any help is appreciated.

Thanks,
Mac

Re: Check If Any Checkbox Is Selected

are your checkboxes from the Forms Toolbar or the Control Toolbox?

Re: Selection Change Event

What is the range that your data is in?

Re: Selection Change Event

hi sstewart949!

I'm not sure what you're asking, but if you're trying to sort the column, the code should look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.EntireColumn.Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Perhaps if you could explain more, we could work out the rest.

Mac

Re: Understanding Code

Hi!
That line pastes only values that have been copied.
Mac

Re: Check If Any Checkbox Is Selected

Try adding the following to your code:

Dim iNum As Long
Dim iCheck As Boolean


For iNum = 1 To 5
        If ActiveSheet.CheckBoxes(iNum) = Checked Then
                iCheck = True
        End If
Next iNum


If Not iCheck = True Then
        MsgBox ("Not checked")
End If

Re: Consolidating Data

Sorry, Everscern, for taking so long to get back. I was called away for a while.

A few more questions:

On the second row of each section, there is a formula in the second column. In the first section of the SMV sheet, you say to exclude this from the transfer. However, you don't say anything about the other 3 sections. Will that value need to be transferred with the row above it?

When a new line of data is entered, will it be entered in the next row after the previous entry or in the next row after the formula?

You say the information will be cleared with a button. Is this true for all 4 sections? If so, when will they be cleared? Will the record be cleared as well? In other words, if the data in the 4 user sections will be cleared at some point and the Record is to be kept permanently, then we don't want to clear the record each time the transfer is made. We will have to find a way to distinguish old entries from new entries in order to prevent data loss and duplication. Please bear with me.

Mac

Re: Consolidating Data

No need to apologize, my friend. These things are never as easy as they seem. I do have an idea, but it would involve adding a column. Would that be a problem?

Mac

Re: Consolidating Data

Okay, I'm confused.:confused:

Will users enter data on several rows or only 1 row? I'm assuming rows will need to be added at some point since there are only 13 rows in the top sections. Will the values in rows 2 & 17 always be there as an example for users or will these be available for data entry at some point?

We will figure this thing out. I'm just having a hard time understanding what it is you need. Is it a record of entries or a record of changes?

Please, if you could explain more, it would be a great help.

Thanks,
Mac

Re: Consolidating Data

I'm not exactly sure what you're wanting, but this is my best guess. Let me know if I'm wrong.

Re: Consolidating Data

See the attachment. Is this what you're looking for?

Re: Consolidating Data

Quote

I tried your above macro but it does not clump into one line.

This is what I meant by the following:

Quote

Do you want to move any row that is changed? Using the Change event will produce several duplicates if more than 1 cell in the row is changed.

Every time you change a cell in the specified range, it will copy the row to the Master sheet. Even when you first enter data.

Quote

how do i edit if I want to copy the row data from more than one worksheet into the master worksheet.

If you paste the code into the worksheet modules of each sheet, the information will be transferred each time a cell is changed on any sheet.

There are a few problems I can think of using the Change Event this way. Can you attach an example of your workbook? Maybe we can come up with something a little more convenient.

Mac

Re: Consolidating Data

Try copying the following into the Worksheet module of sheets 1 and 2. Let me know if this is not what you're looking for.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim SrcRg As Range
Dim Irow As Long
Irow = Target.Row


If Not Intersect(Target, Range("A:K")) Is Nothing Then
        Set SrcRg = Range("A" & Irow, "H" & Irow)
        SrcRg.Copy
        Sheets("Master").Range("A65536").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        
        Set SrcRg = Range("J" & Irow, "K" & Irow)
        SrcRg.Copy
        Sheets("Master").Range("J65536").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Application.ScreenUpdating = True
End Sub

I hope it works for you.

Mac

Re: Consolidating Data

everscern,

I'm working on the code. Is there any special reason for the "Temp" sheet?
I may be misunderstanding, but it seems unnecessary.

Mac

Re: Turning Cell Value Into Number Of Columns

It's not fancy, bur I think this will do it.

Sub Add_Columns()
If Not IsNumeric(Range("B12").Value) Then Exit Sub
If Range("B12").Value = "" Then Exit Sub
Dim i As Long
Dim x As String


x = Range("B12").Value


For i = 1 To x
        ActiveCell.EntireColumn.Insert
Next i
End Sub

Mac

Re: Tab Color Conditional Multiple Sheets

You're very welcome. Sorry I left the sht out. I can't test on 2000 version.

Mac


Edit: Posted before I saw your last response.

Re: If Dates Match Lookup Name Paste Special Value In Associated Column

Maybe this small example will help you get a feel for the code.

Re: Tab Color Conditional Multiple Sheets

Perhaps something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Worksheet
     
For Each sht In ThisWorkbook.Sheets
        If .Range("E7").Value <> 0 Then
                sht.Tab.ColorIndex = 4
        Else
                sht.Tab.ColorIndex = xlColorIndexNone
        End If
Next sht
End Sub

Re: Create A Modified Data Log With Before And After Values

Perhaps something like this example?