EXCEL MACRO – Highlighting the Entire Row and Column that Contain the Active Cell

07/03/2016 § Leave a comment

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8
        .EntireColumn.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True
End Sub

EXCEL MACRO – Highlighting the Row and Column that Contain the Active Cell, Within the Current Region

07/03/2016 § Leave a comment

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    With ActiveCell
        ' Highlight the row and column that contain the active cell, within the current region
        Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 8
        Range(Cells(.CurrentRegion.Row, .Column), Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, .Column)).Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True
End Sub

EXCEL MACRO – Highlighting the Active Cell

07/03/2016 § Leave a comment

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    ' Highlight the active cell
    Target.Interior.ColorIndex = 8
    Application.ScreenUpdating = True

End Sub

EXCEL MACRO – Hide/Unhide Sheet

04/03/2016 § Leave a comment

Sub HideSheetPRIPREMA()

‘ HideSheetPRIPREMA Macro


Application.ScreenUpdating = False

If Not Worksheets(“PRIPREMA”).Visible Then
MsgBox “List je skriven!!!”
‘End If
Else
Sheets(“PRIPREMA”).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(“MENU”).Select
Range(“A1”).Select
Application.ScreenUpdating = True

End If
End Sub

 

=======
Sub UNHideSheetPRIPREMA()

‘ HideSheetPRIPREMA Macro


Application.ScreenUpdating = False
Sheets(“MENU”).Select
Sheets(“PRIPREMA”).Visible = True
Sheets(“MENU”).Select
Range(“A1”).Select
Sheets(“PRIPREMA”).Select
Range(“A30”).Select
Application.ScreenUpdating = True
End Sub

EXCEL MACRO – Save Document

03/03/2016 § Leave a comment

Snima fijl kao .XLSM

Sub SaveMe()
myFilename = InputBox(“Unesi ime fajla u formatu: YYYYMM” & vbNewLine & “” & vbNewLine & “Godina + sledeci mesec ” & vbNewLine & “” & vbNewLine & “npr:   201610”) & “_Breakage.xlsm”

myDir = ThisWorkbook.Path
ActiveWorkbook.SaveAs FileName:= _
myDir & “\” & myFilename, _
FileFormat:=52, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
CreateObject(“WScript.Shell”).Popup “Dokument je snimljen kao ” & vbCrLf & myDir & “\” & vbCrLf & myFilename, _
2, “PORUKA”
‘If user specified file name, perform Save and display msgbox

If filemyFilename <> False Then
ActiveWorkbook.SaveAs FileName:=fileSaveName, FileFormat:=52

MsgBox “Save as ” & fileSaveName
End If

End Sub

EXCEL – Message Box

03/03/2016 § Leave a comment

Message box – new line

Dim answer As Integer
answer = MsgBox(“Are you sure you want to empty the sheet?”, vbYesNo + vbQuestion, “Empty Sheet”)
If answer = vbYes Then
Cells.ClearContents
Else
‘do nothing
End If

Source:
http://www.excel-easy.com/vba/examples/msgbox-function.html

Msgbox Function in Excel VBA

=====

 

Message box – new line

MsgBox “Line 1” & vbNewLine & “Line 2”

New Line
Source:
http://www.excel-easy.com/vba/msgbox.html

EXCEL – Reminder

02/10/2015 § Leave a comment

Application.ScreenUpdating = False
Application.ScreenUpdating = True

..::..

Worksheets(“Menu”).Select
Sheets(“Menu”).Activate
Range(“A1”).Select

..::..

Hide Rows
Rows(“15:20”).Select
Selection.EntireRow.Hidden = True

..::..

Protect / Unprotect Sheet

Sheets(“Menu”).Protect Password: = “22”
Sheets(“Menu”).Unprotect Password: = “22”

..::..

Deselect Range After Macro

Application.CutCopyMode = False

..::..

Formula =SHEETNAME()

Function SheetName()
SheetName = ActiveSheet.Name
End Function

..::..

 

Where Am I?

You are currently browsing the Microsoft Office category at RheXenoR.