Posts by StefanG

Hi,
How can i test in VBA if the active cell has conditional formatting?

Apparently

ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Select

will hightlight all cells that have Conditional Formatting, but i cant figure on how to test the extive cell only.

Thank you,
Stefan

Re: Conditional & Validation Formatting - ISIN - Contains

Thank you very much.
I will look at these suggestions.
Your help is much appreciated.
Stefan

Re: autoreplace "label" name in UserForm

Hi,

Quote

AFAIK yo can't change the Name property like that, you could the caption

Thank you. Although i already changed all 100+ names and captions last night manually, if you could please point out how the captions can be changed all at once would be appreciated. I'm sure it can come handy.

Quote

Why not simply rename each one when you add it?

Yeah, easier said... The project was set-up and used one way. Now it canged and got added a bunch of extra data and hence wanted to rename existing ones to fit better in the bigger picture.

Stefan

Re: Conditional & Validation Formatting - ISIN - Contains

Hi Dave,
Sorry for any confusion caused.
Maybe one prob at a time.

How can I use conditional formatting to detect if a cell contains a certain string? So if a cell content is "NCT","nct", or "05/05/2006 NCT", fire conditional formatting.

Stefan

Hello,
When i insert a "Label" on a UserForm it automatically gets a "Name" and matching "Caption", i.e. "Label1". Can i use find/replace or something else to rename these all at once? Say, find "Label" change to "LBL".
Thank you,
Stefan

Hello,
Conditional formatting:
If a cell contains a string, say "NCT" or "nct", conditional format shall work. The string may be with a date entered i.e. "05/05/2006 NCT"

Same said cell is currently set to only allow dates.
How can i achieve that
Data Validation:
Can data validation be set to only allow a date, and/or "date NCT" as above?

Thank you,
Stefan

Hello,
Can i convert this

ActiveCell.Offset(-1, 0).Copy
               With ActiveCell
            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            .ClearOutline
        End With

So that i can copy/paste the Offset range such as (-1,-2) (-1,1)?
Thank you,
Stefan

Re: Saving Just One Sheet?

Hi guggu,

wb.SaveAs "New Workbook " & Range("G4") & ".xls"

Stefan

Re: Capturing & Using Number of Copies to Print

Hi Oregonian,

Copies:=MyValue

Stefan

Re: "Automatic" Border Coloring Overwritten

Hi Phlu,
Maybe under |Tools |Options |View |Gridlines Color?
Stefan

Re: Conditional formatting of Dates - highlighting of columns

Hi dbirdy,
PMFJI
This is the best i can do, building on jmhans's code. I'm not sure whether you really like to use it in the Worksheet_Calculate, and hence get this recalculated "all the time"....

Dim MyDate, I
    Cells.Interior.ColorIndex = Null
    For I = 2 To 6
        MyDate = Range("Q" & I)
        Columns(WorksheetFunction.Match(CLng(MyDate), Rows(1), False)).Interior.ColorIndex = 6
    Next I

Stefan

Re: Adding data to the same worksheet

Hi,
This is something that i found helpful, put together/refined with help and thanks to Batman and RoyUK in this thread. Maybe you can add this to your code to determine the next row?

Stefan
p.s.
It should be mentioned that this is checking column A for the next empty cell/row. If this cell/colum may have no entries at times, change to a column that allways carries data as to not loose/overwite data.

Sub Next_Row()
Dim FirstBlank As Range
Set FirstBlank = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
FirstBlank.Select
End Sub

Re: VBA - application defined or object defined error

Hi jijy,
I changed EnableAutoFilter to AllowFiltering. I cant seem to find a reference to EnableAutoFilter and EnableEditObjects which seemed to cause a problem. There was twice reference to AllowUsingPivotTables. I changed

Sheets(i).Protect "Protect Sheets" 
        ActiveSheet.Protect '....

to

Sheets(I).Protect "Protect Sheets"'....

So something likes this seems to work just fine ... have a look at Excel VBA Help under "Protect Method" for the different protection options and, of course, adjust as needed.

Stefan

Option Explicit
Sub Protect()
Dim I
    Application.ScreenUpdating = False
    For I = 1 To Sheets.Count
        Sheets(I).Protect "Protect Sheets", _
            AllowFiltering:=True, _
            AllowFormattingColumns:=True, _
            AllowUsingPivotTables:=True, _
            contents:=True, _
            DrawingObjects:=False


        With Worksheets("PPK & DRP")
            .EnableOutlining = True
            .Protect contents:=True, userInterfaceOnly:=True
        End With


    Next I
    Application.ScreenUpdating = True
End Sub


Sub Unprotect()
Dim I
    Application.ScreenUpdating = False
    For I = 1 To Sheets.Count
        Sheets(I).Unprotect "Protect Sheets"
    Next I
    Application.ScreenUpdating = True
End Sub

Re: Hide Every Nth Row

Hi witteman,

How about this - may be overkill, but hey...
You will get a message box in which row to start, in which increments to delete rows and which row is the last to be deleted. Before deleting, you get an option to verify your selection.

Option Explicit
Sub DeleteRow()
Dim EndRow, CheckRows, I, StartRow, StepRow
    StartRow = Application.InputBox _
        ("Enter which row is the first to be removed." & Chr(10), _
        "Rows to delete - Start point", , , , , , 1)
    If TypeName(StartRow) = "Boolean" Then
        Exit Sub
    End If
    
    StepRow = Application.InputBox _
        ("Enter increment of n-th row to delete," & Chr(10) & _
            "i.e. 2 = every other, 3 every third?" & Chr(10), _
        "Rows to delete - Step", , , , , , 1)
    If TypeName(StepRow) = "Boolean" Or StepRow <= 1 Then
        MsgBox "Sorry, do not remove every row with this code."
        Exit Sub
    End If
    
    EndRow = Application.InputBox _
        ("Enter which row is the last to be removed." & Chr(10), _
        "Rows to delete - End point", , , , , , 1)
    If TypeName(EndRow) = "Boolean" Then
        Exit Sub
    End If
    
CheckRows = MsgBox("You want to remove rows in steps of " & StepRow _
    & ", starting with row " & StartRow & "  and ending with row " _
    & EndRow & ". Correct?", vbYesNo, "Verify data!")
    If CheckRows = vbYes Then
        I = StartRow
        Do Until I > EndRow
            Rows(I).Select
            Selection.Delete Shift:=xlUp
            I = I + StepRow - 1
        Loop
    Else
    End If
Application.Goto Reference:="R1C1"
End Sub

To build on this:
Change

'to delete the tows
            Selection.Delete Shift:=xlUp
            I = I + StepRow - 1

to this

' to hide the rows
            Selection.EntireRow.Hidden = True
            I = I + StepRow

and of course the message boxes from reference "deleting rows" to "hiding rows" and vica versa.

Stefan

Edit: small correction: from

"Do Until I >= EndRow"

to

"Do Until I > EndRow"

as it would otherwise omit the last desired row to be deleted/hidden.

Re: VBA code to get worksheet name

Hi,

I'm not sure what factor determines what sheet name to use unless it is always the "active worksheet" name, regardless of the workbook. And, where should the active sheets name go in your formula?

I suppose you can use

Quote

ActiveSheet.Name

to get the sheet name. I would think that you should be able to built that into your formula. As said, I'm not sure where though.

"=LOOKUP(2,1/((" & ActiveSheet.Name & "...

Stefan

Re: Delete a Folder

Hi dgr,
Good to hear you found some help. - I suppose you could try what was mentioned in the same thread, earlier on.

"RmDir "NewPrivateFolder" 
' deletes the subfolder NewPrivateFolder in the active folder

Stefan

Re: Use of {} opposed to ()

Hi roughneck82,
{} - check out "array formula" Excel help.
Stefan

Re: Automatically Run Macro on Formula Result obtained from pivot table

Hi,
Can you build this into your code?

ActiveWorkbook.Sheets("Sheet4").PivotTables("PivotTable1").RefreshTable

Also, do a search here in the forum for your auto-update and emailing. I'm sure you could find some (better) help then mine. I think that you could check the value of your alarm(range) and if it fits your criteria, execute the code for your alarm, i.e. send email. Again, you may be able to find something to auto-email in this forum here. There may be security issues in/around MS/outlook. - Have a look here or here.

I dont know if the below helps you any, but that is what i used to test my make-up PivotTable where the source data was on Sheet1 and the table on Sheet4. This updated, upon executing "Update_P_Table" via button, the table, checked the alarm(range)value automatically and gave the msgbox if the alarm(range)value in my table was meeting the "1" (alarm) criteria.

I hope you find this helpful. I am confident you get better/shorter resolution/feedback from others soon.

Stefan

Sub Update_P_Table()
    ' change to suite your workbook,worksheet and PivotTable names
    Windows("Book1").Activate
    Sheets("Sheet4").Select
    ActiveWorkbook.Sheets("Sheet4").PivotTables("PivotTable1").RefreshTable
    ' executes worksheet_selectionchange/range that contains the alarm value
        Range("F5").Select
    ' sort of resets that sheet for the next update
        Range("A1").Select
    ' sort of resets to the "master" sheet
    ActiveWorkbook.Sheets("Sheet1").Select
        Range("A1").Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("F5")) Is Nothing Then
        If Range("F5") = 1 Then
            MsgBox "alarm" ' replace with email/alarm code
        Else
        End If
    End If
End Sub

Re: Hyperlink/PDF File

Hi dlloyd90,
I dont remember the details involved but i remember something similar to happen in our office. The difference is that the PDF files were not necessarily opened through Excel. I believe this was something within Adobe Reader. You might want to check their site. - The little i remember, in our instance is, that Adobe was set to auto-remind/check for updates and every time, i think once per day or so, when Adobe was opened, it "disappeared" into the background with the question whether the user wanted to update or not and/or install a component or not. Unless you "knew" about the pop-up, maybe ctrl and tab will help to "find" it, it looked as if Adobe was either not responding and/or frozen.
Maybe checking/unchecking the update options will help - Edit | Preferences | Update. And maybe it's something totally different afterall. Good luck.
Stefan

Re: Offset error in one, but not another workbook

Hi Roy,
Thank you, this works. So i suppose you have to have the column reference (1 for A).
Stefan