Free Microsoft Excel 2013 Quick Reference

Selection_Activate

Hi,

I have seen code for Private Sub Worksheet_Change which allows code to be
excecuted as a result of a change to a cell but is there anything that can
identify when a cell is simply selected?
--
Andrew


I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]

I have been trying to do this for quite some time.

I have found that:

Set LastCol =
Range("XFD" & ActiveCell.Row).End(xlToLeft)
    
    Application.Goto Range(ActiveCell, LastCol)
will select active cell to last used cell in the same row. I have tried modifying this to
select a range of the active cell to the last used cell in the same column, non contiguous, but can't seem to figure it out.

Any tips are greatly appreciated.

It's obviously not as simple as:

Set LastRowA =
Range(ActiveCell.column & " 1048576").End(xlUp)
    
    Application.Goto Range(ActiveCell, LastRowA)
I am using excel 2010 and I know 1048576 probably isn't the best way to try this but it's the best I could figure out.

Hello all,

I am having an issue displaying “Highest Value” data label on my scatter chart and the reason for that is because my values are set as:

	VB:
	
ActiveChart.SeriesCollection(1).Values = "=RiskData!$AD$2:$AD$6000" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Because I am looking at the Column AD up to 6000 rows (the only reason I do 6000 is because the value under this column changes daily), applying data label to the chart with this code doesn’t work:

	VB:
	
 
For GCount = 1 To ActiveChart.SeriesCollection.Count 
    ActiveChart.SeriesCollection(GCount).Points(ActiveChart.SeriesCollection(GCount).Points.Count).ApplyDataLabels 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I only select Active Cells under column “AD”, than my data label would show up correctly.

My question is… For the Scatter chart… How do I select ONLY Active Cells (cells with data) under column AD instead of "=RiskData!$AD$2:$AD$6000"?

Hi All,

I have a workbook with 20 sheets out of which 3 are named “Country”, “Region” and “Sub Region”. The remaining 17 sheets contain feedback from users from various countries and regions.

The design of the 17 sheets is as follows:
Users enter their names, countries, regions and sub regions via a validation list in cells D4-D7. They then move on to fill several rows of data (Range “B9:N9”). Some of these are selected via a validation list to reduce input error. I have copied the validation lists and formats from B9:N9 to B109:N109. Users don’t fill 100 entries, but I copied the formats on 100 rows to be on the safe side.

The Country, Region and Sub Region sheets are for summary purposes. A user selects a geographical location, and a macro loops through the 17 sheets and copies entries from “USA” for example.
The problem I’m having is that the macros I currently have copies from Ranges B9:N9 to Ranges B109:N109 onto the destination sheet, so even though I have the results I want, I have empty rows between results.

Please HELP!


	VB:
	
 Roman]Sub country()[/FONT][/SIZE] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]Sheets("Country").Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Range("B12:N3000").Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Selection.ClearContents[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Range("D6").Activate[/FONT][/SIZE] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]Dim ws As Worksheet[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Dim sheetCount As Integer[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Dim j As Integer[/FONT][/SIZE] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]sheetCount = ActiveWorkbook.Sheets.Count[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]j = 1[/FONT][/SIZE] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]Do While j < sheetCount[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]    For Each ws In Worksheets[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]        If ws.Range("D3").Value = Sheets("Country").Range("E6") Then[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            ws.Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            Range("B9:N9").Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            Range(Selection, Selection.End(xlDown)).Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            Selection.Copy[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            Sheets("Country").Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            Range("B3000").End(xlUp).Offset(1, 0).Select[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]        :=False, Transpose:=False[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]        End If[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]        j = j + 1[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]    Next ws[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Loop[/FONT][/SIZE] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]If Sheets("Country").Range("B12") = "" Then[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]    MsgBox ("No Records from " & Sheets("Country").Range("E6").Value)[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]    Sheets("Country").Range("D3").Value = "Feedback By Country"[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Else[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]    Sheets("Country").Range("D3").Value = “Feedback By Country - " &
Sheets("Country").Range("E6").Value[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]End If[/FONT][/SIZE] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I’ve been told to use a “First Row” and “Last Row” to declare the active area on each sheet before copying them over onto the destination sheet. However, my beginner VBA skills limit me from doing that…

Please help!!

This code works fine, however am looking to improve/learn a better way.
i.e. The Query is on a hidden Worksheet, Do I have to unhide, select and refresh as below or is there a better more efficient way?

Code:
Sub WOInfoTranspose()
    Dim ws As Worksheet
    Dim WOITarray() As Variant
    
    Set ws = ActiveSheet
    
    Application.ScreenUpdating = False
        wksWOQuery.Visible = xlSheetVisible
        wksWOQuery.Activate
        wksWOQuery.Range("D8").Select
        Selection.QueryTable.Refresh BackgroundQuery:=False
        
        WOITarray = wksWOQuery.Range("c7:aa8").Value
        wksWOQuery.Cells(1, 1).Resize(25, 2) = Application.Transpose(WOITarray)
        
        ws.Select
    Application.ScreenUpdating = True
    
End Sub
Thanks,
Chas

Is there are way to select the active cell and the cell to the right.

Not sure how to right thie stuff??

Acl = Range.ActiveCell

Range(Acl & Offset(1,0)).Select

Am I even close?
This is my poor attempt at code!!!

Michael

Hello,

I am trying to write a macro that would select the active range of cells in columns A:J. When I record the macro and hit Control+A it only records those cells that are active in this situation. But if next week I have 5 additional rows this would not work. Could someone assist me? Thanks so much!!

the following code works by it's self but not as part of the whole macro
Code:
Workbooks.Open ("C:Documents and Settingsj.doeDesktopFutures TALXOut Going TALX File.csv")
Sheets("Out Going TALX File").Select
Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
when run as part of the larger macro it selects the entire sheet rather than the active (used) range

here's the whole thing
Code:
Sub Export_TALX()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

CFILE = ActiveWorkbook.Name
SNAME = "sheet1"
x = Dir("C:Documents and Settingsj.doeDesktopFutures TALXOut Going TALX File.csv")

Windows(CFILE).Activate
Sheets("sheet1").Select
A = Range("A3: O1003")

Workbooks.Open ("C:Documents and Settingsj.doeDesktopFutures TALXOut Going TALX File.csv")
Sheets("Out Going TALX File").Select
Range("A1: O1000") = A
Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select

Workbooks(CFILE).Close

Application.ScreenUpdating = True

End Sub
also, anyone know how I can set A as the active range only instead of copying the whole range ("A3: O1003")

thanks

Hi

I currently have a workbook which has multiple worksheets, each sheet having an individual name based on a date e.g. 071004, 081004.

I am try to passing information from each of these sheets to a single sheet so the data can be loaded into a new software package. Currently my script is able to select the current active sheet and pass the information but I am not to sure how to select the next sheet and so on and so on

I am sure it is quite simple but am not to sure of the syntax, any assistance would be appreciated

Thanks

hi all,

some help with the code below, what i need it to do is when a cell is selected (so when you click once on it) it unhides a hidden sheet and takes you to a certain range. It does not seem to work. Also this code will be placed into 12 sheets.

It appears that what ever cell i click it unhides the hidden sheet and stops the macro.

Thanks
Paul

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B11").Select Then
Sheet3.Visible = True
Sheet3.Activate
Range("B2").Select
Else
End If
If Range("B12").Select Then
Sheet3.Visible = True
Sheet3.Activate
Range("D2").Select
Else
End If
If Range("B13").Select Then
Sheet3.Visible = True
Sheet3.Activate
Range("D2").Select
End If
End Sub


Hey guys and gals, Thanks for lookin at this problemmmmm???

When I am writing a macro using visual basic, how do I select a range which includes 2 rows down and 8 rows across (selects the range not just the single cell) from the currently selected/active cell then copy it: All this starts from the cell the user starts from.
Example......

PuzzeledProgramer

I am trying simply to select a range with Application.InputBox method. It does ask to select the range and correctly specify it in a pop up window, but in reality it selects only the active cell (see two attached screen shorts, MsgBox shows number of selected counts). What is wrong?

I work with Excel 2007 on WinXP.

Thank you very much in advance!

The code is :

===================================
Public Sub ProblemWithInputBox()

Dim UserRange As Range

Prompt = "Select one column range to make list of categories"
Title = "Select a range"

On Error Resume Next
Set UserRange = Application.InputBox( _
    Prompt:=Prompt, _
    Title:=Title, _
    Type:=8)
On Error GoTo 0

MsgBox Selection.Count

End Sub 
=================================


Hi,

When the user makes a choice (selection) in the combobox he should activate a sub depending on his choice. How can I do this ?

  Sheet1.ComboBox1.AddItem "(All)"
  Sheet1.ComboBox1.AddItem "Actual "
  Sheet1.ComboBox1.AddItem "Actual Month"
  Sheet1.ComboBox1.AddItem "Actual Month Country"


Please help me. I need to select a range of cells in a row:
For example:from a5 to j5.
I want to have "A5" as "active.cell" and write a macro to select from the
"Active.Cell" to the desired range end.

Is it possible to use "Offset" or other type of commands.

Please if anyone can help.

Kind regards,
--
Carlos

How can I select only the active area of a spreadsheet without using the
mouse. Using ctrl+alt+spacebar gives me the entire sheet, which is way too
much, as I only want that portion containing data. Any help accomplishing
this would be GREATLY appreciated!

What is the quickest way to eliminate .Select, .Selection and .Activate from code to increase efficiency?
How do I refer to the range and workbook without selecting them first?

I would really appreciate some help.

Thanks

Hi

I would like the user to be able to select any cell they wish and then click on a button to active my macro.

The problem I am having is getting the value of the selected cell e.g. D4

All I can get is the value 4.

I would then like to add this to a range so that I can use a loop to interigate the data.

For example:

If the user selected the cell D4 and the data went all the way down to D100 my range would be D4:D100

Here is my code (I had help from a previous post to get this far!)

Sub SearchString()

'Sets the cells in range from top to bottom
MyRange = "D4:D100"

'Declares Count as an Integer
Count = 0

'Declares CountNull as an Integer
CountNull = 0

For Each myCell In Range(MyRange)

'If two or more cells have a Null value in sequence then the loop ends
'Amend the value to 'x' for greater Null cells
If CountNull = 2 Then
Exit For
End If

'Checks if cell has a Null value
If myCell < 1 Then
'Allows one cell to be Null
CountNull = CountNull + 1
'Skips a line
GoTo NextLoop
Else
'Resets value so that the count is correct
CountNull = 0
End If

'searches for the occurance of tlp or TLP in the string
If InStr(myCell, "tlp") > 0 Or InStr(myCell, "TLP") > 0 Then
myCell.Offset(0, -2) = "Yes"
'Adds one to the count
Count = Count + 1

Else
'if neither is found then No is returned
myCell.Offset(0, -2) = "No"

End If

'searches for the occurance of tnt or TNT in the string
If InStr(myCell, "tnt") > 0 Or InStr(myCell, "TNT") > 0 Then
myCell.Offset(0, -3) = "Yes"
'Adds one to the count
Count = Count + 1
Else
'if neither is found then No is returned
myCell.Offset(0, -3) = "No"
End If

'Checks the value of Count, IF 2 then both strings have been found

If Count = 2 Then
'Count is two so BOTH strings have been found
myCell.Offset(0, -1) = "Yes"
Else
'Value of Count is either zero or one
myCell.Offset(0, -1) = "No"
End If

'Re-sets the value of count ready for Next Loop
Count = 0

NextLoop:
Next myCell

End Sub

I hope I have explained this clearly enough!

Just being able to work out the actual cell value would be great e.g. D4

I'd like to execute a series of commands without pulling the various screens worked on to the front. The user needs to see no screen flips to the sheets referenced.

I believe I need to replace all my "activesheet." and "selection." commands with specific sheet references ... but all I get is errors when I try. There must be some rhyme or reason, but I am not seeing it. Guidance please.

TIA
WD

********************************************

The following blocks of code need to be modified:

1)
Worksheets("Usage Log").Activate
With ActiveSheet.AutoFilter.Range

2)
Sheets("Usage Log").Select
Selection.AutoFilter Field:=2, Criteria1:=date_selection

3)
Worksheets("Usage Log").Activate
ActiveSheet.Cells.Select
Selection.AutoFilter

4)
Sheets("Usage Log").Select
Range("AH6:AX35").Select
Selection.ClearContents

Hi All,

I have a workbook with 20 sheets out of which 3 are named “Country”, “Region” and “Sub Region”. The remaining 17 sheets contain feedback from users from various countries and regions.

The design of the 17 sheets is as follows:
Users enter their names, countries, regions and sub regions via a validation list in cells D4-D7. They then move on to fill several rows of data (Range “B9:N9”). Some of these are selected via a validation list to reduce input error. I have copied the validation lists and formats from B9:N9 to B109:N109. Users don’t fill 100 entries, but I copied the formats on 100 rows to be on the safe side.

The Country, Region and Sub Region sheets are for summary purposes. A user selects a geographical location, and a macro loops through the 17 sheets and copies entries from “USA” for example.
The problem I’m having is that the macros I currently have copies from Ranges B9:N9 to Ranges B109:N109 onto the destination sheet, so even though I have the results I want, I have empty rows between results.

Please HELP!

Sub country()

Sheets("Country").Select
Range("B12:N3000").Select
Selection.ClearContents
Range("D6").Activate


Dim ws As Worksheet
Dim sheetCount As Integer
Dim j As Integer

sheetCount = ActiveWorkbook.Sheets.Count
j = 1

Do While j < sheetCount
    For Each ws In Worksheets
        If ws.Range("D3").Value = Sheets("Country").Range("E6") Then
            ws.Select
            Range("B9:N9").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Sheets("Country").Select
            Range("B3000").End(xlUp).Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        End If
        j = j + 1
    Next ws
Loop

If Sheets("Country").Range("B12") = "" Then
    MsgBox ("No Records from " & Sheets("Country").Range("E6").Value)
    Sheets("Country").Range("D3").Value = "Feedback By Country"
Else
    Sheets("Country").Range("D3").Value = “Feedback By Country - " &
Sheets("Country").Range("E6").Value
End If

End Sub
I’ve been told to use a “First Row” and “Last Row” to declare the active area on each sheet before copying them over onto the destination sheet. However, my beginner VBA skills limit me from doing that…

Please help!!

Hi

I have already posted a query like this before but dint get a response...so im trying my luck again.

I have some worksheets in a workbook which contain some charts which I have to copy onto a powerpoint presentation. I have to copy one chart onto one slide and then the next chart from the next sheet onto the next slide and so on. Im using a macro which selects either the current slide as active slide (i.e. copies all the charts onto one slide) or appends the slides to the end of the presentation. This isnt what I want and i am trying to figure out if I can select the active slide in that presentation by slide number. Unfortunately I havent been able to do it.
Any ideas on this would be appreciated. Thanks.

P.S.: Here is the macro i am using

Sub
Copy_Paste_to_PowerPoint2()

'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
Dim ppApp As PowerPoint.Application
Dim ppSlide As PowerPoint.Slide

Dim SheetName As String
Dim TestRange As Range
Dim TestSheet As Worksheet
Dim TestChart As ChartObject

Dim PasteChart As Boolean
Dim PasteChartLink As Boolean
Dim ChartNumber As Long

Dim PasteRange As Boolean
Dim RangePasteType As String
Dim RangeName As String
Dim AddSlidesToEnd As Boolean
Dim shts As Worksheet
'Parameters

'SheetName - name of sheet in Excel that contains the range or chart to copy

'PasteChart -If True then routine will copy and paste a chart
'PasteChartLink -If True then Routine will paste chart with Link; if = False then paste chart no link
'ChartNumber -Chart Object Number
'
'PasteRange - If True then Routine will copy and Paste a range
'RangePasteType - Paste as Picture linked or unlinked, "HTML" or "Picture". See routine below for exact
values
'RangeName - Address or name of range to copy; "B3:G9" "MyRange"
'AddSlidesToEnd - If True then appednd slides to end of presentation and paste. If False then paste on current slide.

'use active sheet. This can be a direct sheet name



SheetName = Sheet2.Name

'Setting PasteRange to True means that Chart Option will not be used
PasteRange = True
RangeName = ("b2:g16") '"MyRange"
RangePasteType = "Picture"
rangelink = True

PasteChart = False
PasteChartLink = True
ChartNumber = 1

AddSlidesToEnd = False


'Error testing
On Error Resume Next
Set TestSheet = Sheets(SheetName)
Set TestRange = Sheets(SheetName).Range(RangeName)
Set TestChart = Sheets(SheetName).ChartObjects(ChartNumber)
On Error GoTo 0

If TestSheet Is Nothing Then
MsgBox "Sheet " & SheetName & " does not exist. Macro will exit", vbCritical
Exit Sub
End If

If PasteRange And TestRange Is Nothing Then
MsgBox "Range " & RangeName & " does not exist. Macro will exit", vbCritical
Exit Sub
End If

If PasteRange = False And PasteChart And TestChart Is Nothing Then
MsgBox "Chart " & ChartNumber & " does not exist. Macro will exit", vbCritical
Exit Sub
End If


'Look for existing instance
On Error Resume Next
Set ppApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0

'Create new instance if no instance exists
If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
'Add a presentation if none exists
If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add

'Make the instance visible
ppApp.Visible = True

'Check that a slide exits, if it doesn't add 1 slide. Else use the last slide for the paste operation
If ppApp.ActivePresentation.Slides.Count = 0 Then
Set ppSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
Else
If AddSlidesToEnd Then
'Appends slides to end of presentation and makes last slide active
ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
Else
'Sets current slide to active slide
Set ppSlide = ppApp.ActiveWindow.View.Slide
End If
End If

'Options for Copy & Paste Ranges and Charts
If PasteRange = True Then
'Options for Copy & Paste Ranges
If RangePasteType = "Picture" Then
'Paste Range as Picture
Worksheets(SheetName).Range(RangeName).Copy
'ppSlide.Shapes.PasteSpecial(ppPasteDefault, link:=rangelink).Select
'ppSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile, link:=rangelink).Select
'ppSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile, , link:=rangelink).Select
ppSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile).Select
Else
'Paste Range as HTML
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteHTML, link:=rangelink).Select
End If
Else
'Options for Copy and Paste Charts
Worksheets(SheetName).Activate
ActiveSheet.ChartObjects(ChartNumber).Select
If PasteChartLink = True Then
'Copy & Paste Chart Linked
ActiveChart.ChartArea.Copy
ppSlide.Shapes.PasteSpecial(link:=True).Select
Else
'Copy & Paste Chart Not Linked
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
ppSlide.Shapes.Paste.Select
End If
End If

'Center pasted object in the slide
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

AppActivate ("Microsoft PowerPoint")
Set ppSlide = Nothing
Set ppApp = Nothing

End Sub
PLEASE HELP.

Hello All,

When I am in Design mode, excel knows the name of the object that I click, i.e. when a checkbox is double-clicked excel adds the sub CheckBox12_Click() skeleton to my VBA code.

Does VBA have a command that will allow me to collect this same information when a user clicks the same box during real-time operation of the sheet? Is there a method that returns the name of the shape/control object that is selected/activated?

Thanks in advance,
Aaron

Hello,

I am trying to do something I believe is very basic but struggling.

I would like to select a worksheet that has been listed in another worksheet in a particular cell.

For eg, the worksheet name I would like to select is "Sheet 3" and I have written "sheet 3" in cell A1 in sheet 1. If I change the cell in sheet 1 range A1 to "sheet 2", then I expect sheet 2 to be selected.

Could you please help with the vba code.

Thank you.

A custom UserForm pops up when a cell is double-clicked. The form contains a ListBox that presents the user with a list of values to select. When the UserForm first pops up I want the item that matches the value of the activecell to be highlighted/selected.

Any ideas appreciated.

Thanks in advance

Tom

I have a piece of clip art in a cell (that moves around in the right cell when i filter/sort the data)

i need to know how to select/activate the cell that a piece of clip art housed in.

is there a way to say at the beginning of the macro to select the cell behind the picture?

Macro works by offsetting a few cells to the left and pulling the name of the item in that row and then going to another worksheet that is named the same thing as what is in the cell i offset to.

If i click on the cell behind the picture first then it works but if i have a diff cell activated when i click the picture it of course doesnt work - so all i need is a way to activate the cell behind the picture.

Good Afternoon,

I have a routine that highlights a selected Active Cell,
but when I printout the spreadsheet this highlight is not printed.
I have been pull my hair out (what's left!), to try to show this highlighted cell when printed.............

Here's the code I use.


	VB:
	
 Range) 
    If Intersect(Range("d22:u45"), Target) Is Nothing Then 
         
        Range("d12").Value = "       Out" 
        Range("e14").Value = "0" 
         
         
    Else 
        If Intersect(Range("d22:u45"), Target) >= 3 Then 
            Range("d12").Value = Cells(19, ActiveCell.Column).Value 
            Range("e14").Value = Cells(21, ActiveCell.Column).Value 
            Static rngPrev As Range, PrevColor As Integer 
            Dim TempColor As Integer 
            TempColor = Target.Cells(1, 1).Interior.ColorIndex 
             
            If Not rngPrev Is Nothing Then 
                If PrevColor  xlLineStyleNone Then 
                    rngPrev.BorderAround ColorIndex:=PrevColor 
                Else 
                    rngPrev.Borders.LineStyle = xlLineStyleNone 
                End If 
            End If 
            PrevColor = TempColor 
            Target.BorderAround Weight:=xlThick, ColorIndex:=3 
             
            Set rngPrev = Target 
             
        Else 
            Range("d12").Value = "not possible" 
            Range("e14").Value = "0" 
        End If 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So you Excel wizards out there, how do I get it to print the new boarder around the active cell?

Mike