Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Set VBA Range As Cells Selected By Cursor


The VBA code (in the code window) runs nicely on the range B10:B1000, but I'd prefer that it only run on a range I define by the cells that are currently highlighted/selected on the active sheet. How should the line of

Set SHOPS = Range("B10:B1000")


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Set Range From Selected Cells/Range
I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12")

rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work.

Set rngData = Range. CurrentRegion.Select

View Replies!   View Related
Cursor Movement :: Move To Selected Cells Only
to make cursor to move only on the selected cells.

E7, O21, O22, O23, O24, Z23, Z24, Z26, Z27, Z28, C26, C27, C28 ...

View Replies!   View Related
Set Active Cursor In TextBox (blinking Cursor)
I am working with a VBA userform and several textbox's, setting SetFocus and or TabIndex doesn't leave the box ready to accept input and there is no cursor shown to indicate it is ready to accept input.

View Replies!   View Related
Set Print Area From Selected Range
I have some code which selected a certain amount of columns depending on
whether they contain values or not. Once I have this range set and I select
it I want to set this as the print area. I am not quite sure how to do
this. for example I tried:

rngUnion.Select
ActiveSheet.PageSetup.PrintArea = ActiveCell. CurrentRegion.Address

View Replies!   View Related
Set A Range Based On Area Selected Using Mouse?
I'm trying to set a variable for a range based on the cells selected. For example, I want variable "myrange" to be set to range("A1:D14") if I click on cell A1 and drag it to D14. So far everything I've tried ends up getting the good old "1004 - Application defined or object defined error".

View Replies!   View Related
Set Focus Of Cursor In TextBox On UserForm Show
I'm having some trouble setting up my Userform. It's used for someone to insert a password to open up some sheets. I have it working except for 2 problems.

First off it gets started by a sub which has a shortcut key, CTRL + W.

When it opens I would like the TextBox to be ready to be typed on... and I would like the submit button ready for Enter. So when it opens, you just type in the password really quick and press enter without using the mouse at all.

The TextBox starts on the first try, but when I press cancel and then open the UserForm up again, then the cancel command is still selected and I have to select on the textbox.

There is 3 things on this userform... TextBox1, CommandButton1, CommandButton2. All I want is for the TextBox always to show up ready for typing, and the Submit(CommandButton1) to always be ready to press enter.

View Replies!   View Related
Vba Return Name Of Range Selected Cell
I have 12 named ranges on a sheet, ArrM1 thru ArrM12.
Each named range is same size, 6 Rows by 7 columns.

Q. If user selects a cell on sheet that is in one of these ranges, what is code to return name of range?
e.g. ArrM1 is cells c10:i15, user selects cell d12; I would like vba to return the name of the range "ArrM1".

Reason, I have the code below so if user selects cell in ArrM1 code is executed, but rather than reproduce code 12 times for each range I thought I could first see where user selects and then change variable for vrange,
Set vrange = wksYearlyCalendar.Range("ArrM1") to
Set vrange = wksYearlyCalendar.Range("ArrM" & x)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CalDaySel As String, CalDateSel As String
Dim vrange As Range
Dim cell As Range

View Replies!   View Related
Importing Selected Cells Into VBA
Can you help with how to import into vba just the data in the currently selected cells?

What I want to do is to manually select a range of rows (in a single column) and upon running the macro put the data from each cell into the uppermost cell with each seperated with a comma.

I can manage the combining of the data but don't now the selection is read into VBA.

View Replies!   View Related
VBA To Identify Top Left Cell Of A Selected Range
if a user has selected a range, how do you, in VBA, identify the:

1. Top left cell
2. Bottom left cell
3. Top right cell
4. Bottom right cell

For example if user has selected the range B5:M30, then we would want to identify in the macro:

1. Top left cell = B5
2. Bottom left cell = B30
3. Top right cell = M5
4. Bottom right cell = M30

View Replies!   View Related
Set Range For This Vba Code
Sub ReplaceUnderScores()
Selection.Replace What:="_", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

I want it to do this for range AL2 through AL2000. Ive tried a few codes trying to get it to work, but cant figure it out.

View Replies!   View Related
Paste A Selected Range Of Cells
I think I have the comand to select a range of cells, but can not figure how to paste this selection later in the spreadsheet.

This is how the application works.

I have a spreadsheet that I am using as a template. The first 10 rows have to be repeated later in the same spreadsheet after I make a manual page break.

I the spreadsheet I am doing the following commands:
With xlApplication.ActiveSheet
xlPageBreakManual
.Rows(istartrow).Pagebreak = xlPageBreakManual
End With

View Replies!   View Related
How Do I Total Selected Data From A Range Of Cells?
On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100).
I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC.

I need a list on worksheet 2 for each individual person showing;

(1) total number of cells marked C
(2) total number of cells marked C & NYC combined.

View Replies!   View Related
Macro Leaves All The Cells In The Range Selected
I have a worksheet with ever expanding data - rows at the bottom of the data
are continually added. I have a simple macro that sorts all of the data
according to preset parameters and selects the next blank cell in column A,
ready for more data:

Sub Macro5()
Range("SortRange").Select
Selection.Sort Key1:=Range("SortRange"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Do Until ActiveCell.Value = IsEmpty(True)
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

When running the Macro, this leaves all of the cells in the range 'selected'
(ie; coloured-over). What do I need to add to the Macro to just select the
cell in Column A and remove the highlighting from all the other cells?


View Replies!   View Related
VBA To Copy Selected Cells To Clipboard With Text Data
Need aid in copying certain cells with text/labels to clipboard to format a body of email to be sent via Yahoo webmail, so I can paste it where needed.

I did a couple of Excel VBA's back in 2000 or so at last job but haven't seen/used VBA since.

Have a spreadsheet with customer data, (one cust per row) and wish to create something like the following in clipboard (in this example, assuming row 2 is the selected row)...I'll only be sending out one or two of these per day so nothing fancy is needed, just a way to avoid retyping critical data that could get mistyped.

--------------------
A8

Rental details for 2008

Customer Name : A2
Unit nbr : A3
Nbr of weeks : A4
Balance due : A5
--------------------

I'm using Office 2000 still...I've done some searching but results found seem to complicated for my situation or not quite close enough for me to see how to apply it.

View Replies!   View Related
Elseif Statement In Vba: If The Selected Cell Falls Between 1/01/06 And 31/01/06 Then Jan Would Be Selected
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.

Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................

View Replies!   View Related
Fill Userform Textboxes From Select Cells In Selected Range
Arised from my earlier posting in Populate ComboBox With Specific Sheet Column Range. I have the following working code below, but am having trouble finding coding examples to select specific cells from the selected row (that was found by selecting a ComboBox value)and update TextBoxes with those individual values after the UserForm has been initialized (the bold "GREEN" comment in the code below). I have been able to find plenty of references to update TextBox values to Cells, but that doesn't do me much good in this application since the User needs to verify the old data in these cells before updating them using the UserForm TextBoxes.

I was toying around with several different variations of code (none of which worked properly), so I left it out for clarity of my working code. I'll post up this non-working code as needed, because I really wanted a fresh answer...not what I was trying to do. The attached file should be sufficient to see what's going on

Private Sub UserForm_Initialize()
With Sheets("SR Information")
.Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "MyRange"
End With
SRnumber.RowSource = "MyRange"
End Sub

Private Sub SRnumber_Change()
Dim ServiceRequestNumber As String
Dim c As Range
Dim rngG As Range
Sheets("SR Information").Select
With Selection
ServiceRequestNumber = SRnumber.Value
For Each c In Intersect(ActiveSheet.UsedRange, Columns("a"))
If c = ServiceRequestNumber Then..................

View Replies!   View Related
VBA Change Font Size Of A Character Or Letter Within Any String In Selected Cells
Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.

Also looking to delete the last "•" char(0149) within any string of selected cells.

What would the syntax for these two functions be?

View Replies!   View Related
VBA Error - Unble To Set The Locked Property Of The Range Class
I am having trouble with a macro for unprotecting a sheet keeping specific cells locked then clearing contents of other cells then reproect the sheet. I wrote one for one sheet which works and I have re used this but it keeps saying Unable to set the Locked property of the Range class.

View Replies!   View Related
VBA Code To Set Unknown Range Based On Current Date In 1 Column
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).

View Replies!   View Related
Cursor Movement In Vba
I am wanting VBA language in a macro that will move the cursor a certain number of cells in a certain direction. For example, I want the cursor to move right one cell, no PARTICULAR cell, just right one cell. Is there something I can use?

View Replies!   View Related
Control Cursor Movement In Vba
I want to move the cursor in my spreadsheet from cell to cell in a particular order. I've tried the following code, but it only works when I change the value in the cell.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address()
Case "$A$1"
Range("$F$5").Select
Case "$F$5"
Range("$B$12").Select
Case "$B$12"
Range("$A$6").Select
Case "$A$6"
Range("$A$1").Select
End Select
End Sub

I want the cursor movement to follow the same order even when I don't change the cell value, for example, when I repeatedly hit the "Enter" key without altering the existing cell value.

View Replies!   View Related
Vba Detect When A Specific Set Of Cells Gets An X Typed Into It And Calculate 1 Cell = 36.50 Hours
I'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.

View Replies!   View Related
Copy A Selected Range Of Cells On Sheet One To A Range On Sheet Three
Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?

View Replies!   View Related
Cursor Brings Up Multiple Cells
when i position the cursor over a cell, i get muliple cells highlighted. i enter data and it is entered in the cell i want but the others being highlighted is confusing. i checked and i am not in extended mode.

View Replies!   View Related
Cursor Movement :: To Defined Cells Only
In sheet1, The cursom must move only following cells

A2,A5, C10 C15, D15, E50

Besides this the cursor should not move to any cell.

It must remain between the cells that I described above.



View Replies!   View Related
Set ConnectorFormat Start & End To Selected Shape
I have two Textboxes created from the drawing toolbar, named "FlowChart1", "FlowChart2". Below is a simple macro I recorded and modified which connects these two textboxes.

Sub FlowChartConnection()
ActiveSheet.Shapes.Range( Array("FlowChart1", "FlowChart2")).Select
ActiveSheet.Shapes.AddConnector(msoConnectorCurve, 149.25, 146.25, 159.75, _
257.25).Select
Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes( _
"FlowChart1"), 3
Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("FlowChart2" _
), 1
End Sub

Although it works fine... A problem I am having is that I am working with multiple (duplicated) flowcharts. Currently, the connection is based on the name of the objects. If I duplicate both textboxes and run the macro, the connection will connect to the first set of ( already connected) textboxes.

Q. Is it possible to form a connection based on active selection rather than the name of the objects? ie: if any two textboxes are selected.. connect these two objects.

View Replies!   View Related
Cursor Movement :: Move Multiple Cells
Is there a way to make it so when i hit enter my cursor moves 7 cells instead of 1?

View Replies!   View Related
Change Mouse Cursor When Passing Over Specific Cells
I would like to change the mouse cursor when the pointer passes over (without clicking) some cells which have double-click-event script attached.

I know how to change the cursor with a custom one. The pb is for me to identifie that the pointer is over the cell so as to launch the cursor change macro (and reverse when going over another cell).
Excel does that, for ex, with commented cells but can VBA do it also?

View Replies!   View Related
"Unable To Set The Hidden Property Of The Range Class" When Unhiding Cells
I'm trying to unhide all the cells in a worksheet, but keep getting the "Unable to set the Hidden property of the Range class" error and I'm not sure what to do.

View Replies!   View Related
Deleting Range Of Cells Using VBA
I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:

Workbooks.Open Filename:=fNameAndPath, UpdateLinks:=1
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath2 & myFile3 & myExt, FileFormat:=xlNormal, CreateBackup:=False
Windows(myFile3).Activate
Cells.Select.Range ("A78:K153")
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath4 & myFile4 & myExt2, FileFormat:=xlCSV, CreateBackup:=False

View Replies!   View Related
Highlighting Range Of Cells In VBA
What is the line of code in VBA that allows me to highlight a range of cells (in a column) to the end of the range? e.g. Selection is A2, highlight to end of range in column A. I know this is simple, but I'm new to VBA. Unsuccessful searching the posts.

View Replies!   View Related
Add A Textbox At The Current Position (selected Cell) With A Set Size, Fill Color, And Border Color
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:

View Replies!   View Related
VBA - To Border Cells With Entry In A Given Range
Using VBA how could I put a border around any cell that isn't empty within the range B1:C500. (Worksheet name - Floc BOM)

View Replies!   View Related
VBA To Look For Blank Cells In Range And Copy With Value Above
Is there a VBA macro that can be written that will look in a range of cells in a coloum, look to see if its blank and then copy the value from the cell right above it into it?

View Replies!   View Related
Use More Than 1 Range Of Cells When Writing A VBA Macro
Just wanted to know if I could have a range or ranges which are split. The current range i have is N5:AV6. I need to miss out every 6th cell so am looking to do a number of ranges like this - N5:R6, T5:X6, Z5:AD6 etc etc. If this can be done could you show me how I would need to lay it out?

View Replies!   View Related
VBA - Defining The Range Of Cells Containing Formulas
Using the SpecialCells method with a Type = "xlCellTypeFormulas", I can identify the range contains cells with formulas.

But when there isn't any of these cells in the range you've applied the SpecialCells method to, you get a VBA Error 1004. (Doing this outside VBA gives you a "No cells found" box.) How can I skip over this error?

I'm looping through each sheet in the workbook, and defining the UsedRange. I'm doing another loop that uses the HasFormula property on each cell and then performing an action if HasFormula = True, but this results in a lot of extra work processing cells that don't have formulas.

I'd like to further refine the process by defining the range of cells on each sheet that have formulas. But if a sheet contains no cells with formulas, I'd like to skip past it and move on.

I think this is just a simple syntax / code construction question.

View Replies!   View Related
VBA Error And Saving A Range Of Cells As New Workbook
I have previously tried to save the whole original workbook with the following
ActiveWorkbook.SaveAs Filename:="C:UsersPhilDocumentsMy Excel DocumentsRostersIndividual Rosters" & StaffLastName & " " & StaffFirstName & " - Roster commencing " & Format(DateSerial(Range("X11").Value, Range("V11").Value, Range("T11").Value), "dd mmm yyyy") & ".xlsm

but receive a run-time error message:

Run-time error '1004': Microsoft Office Excel cannot access the file

My first request for help is how is the code wrong and how can it be corrected?

Secondly, instead of saving the whole workbook, is it possible to save just a given range of cells as a new workbook, using the format above to give it a personalised filename?


View Replies!   View Related
Error 1004 Using Range.Cells() Properties In Vba
I keep getting the following error : Run time error'1004'; Application-defined or object-defined error when using the following

View Replies!   View Related
VBA To Apply Conditional Formating To A Range Of Cells
I have used VBA to apply conditional formating to a range of cells. i.e if cell B14 <>"" then row 14 is pale blue untill cell V14 is populated with the time then it removes the formating. One criteria is that if Q14(21/08/06 20:00)>NOW(AA2) and < NOW+1(AA3) then Row turns green(i.e. is due in the next 24 hrs).

Problem is that this formula is applied after a field is updated. When 21/08/06 20:00 comes and goes the row remains green unless I update one of the fields along that row. What would I have to do to make the formating change back automatically when the critera is no longer being met. Here is my codethere are 2 other if statements similar to this with in this code but this is the only part that shouldn't require any user input for the formatting to occour)

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B13:AP162"
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "AP").Value = "YES" And _
Me.Cells(.Row, "Y").Value <> "" Then
Me.Cells(.Row, "B").Resize(, 30).Interior.ColorIndex = 43 'mad green
Else
End If
End With..................

View Replies!   View Related
Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells
Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View Replies!   View Related
Run VBA Code Any Time A Particular Range Of Cells Is Changed
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?

I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.

1. Is there a way to call VB (say, sheet1.myFunction())?

2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)

The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.

View Replies!   View Related
Set Range Variable To Growing Dynamic Range
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

View Replies!   View Related
Vba: Why There Is An Endless Loop When For Each Cell In Range Includes Merged Cells
When I set a "for each cell in range" to go through the range, and if there are merged cells in this range, this will result an endless loop.

View Replies!   View Related
Create From The Data Set In Column Set 1 The Data Set Shown In Colunm Set #2
My data is in colunm set #1. I want to create from the data set in column set 1 the data set shown in colunm set #2. I want to automate the process. The program needs to regognize if a name appears only once or more than once. If it appears more than once the data needs to be merged as I highlighted from yellow to green.

View Replies!   View Related
VBA Macro: Clear All Constant Cells In Range That Don't Have Formulas
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

from another thread (which is excellent!)

- SHG's recommendation of using a named range, for example:

Range("Inputs").ClearContents

Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents

View Replies!   View Related
Set A Range And Find All Numbers In That Range
I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time?

2/18/1975, 156.7519, 175.8083, 235.4675, 253.765
4/11/1973, 237,7642, 346.5342, 113.3145, 321.435
5/12/1985, 342.7625, 123.4233, 253.4321, 357.234
etc.
etc.
etc.


View Replies!   View Related
VBA Code To Email Selected Items
I have the following code that carries out a selection and then brings up in a print preview.

View Replies!   View Related
Vba To Change Color Of Selected Cell
As an example:

Cells C7:F7 are currently shaded light green. Cells G7:L7 are shaded light blue. M7 is shaded dark blue. N7:Q7 are shaded light yellow and R7:T7 are shaded light gray.

When any cell in that range (C7:T7) is clicked (selected), I want the cell color to change to it's normal color; like light yellow to yellow, light blue to blue, dark blue to blue, light green to green and finally light gray to gray.

These cells represent headings for a database. When a heading is selected, the database will sort by that column and the header will change colors. I can do the sort code.

If another header is chosen, the previous selection will need to revert back to it's lighter color and the new selection will change as above... so only ONE cell will be changed from it's "normal state" color at a time. This will serve as an indicator as to which column is being used for the sort. To the user, it will have the appearance of switching on and off.

If any other cell on the sheet is selected, then this should not trigger an event change.

View Replies!   View Related
Viewing And Printing Selected Worksheets - VBA
I have a workbook containing a number of spreadsheets. Some of the spreadsheets are user inputs. The results of the user inputs drive a number of final reports. The final reports (i.e. spreadsheets) are hidden from the user (I don't want the user to be overwhelmed with so many tabs when they open the excel spreadsheet).

I created on the main input tab spreadsheet the following:

1. Check boxes - so that user can select after making his/her inputs the reports that he/she wants to view or print.

Say there are 4 reports (call them Sheet1, Sheet2, Sheet3, Sheet4 - therefore, 4 check boxes. Through the Format Control, the checkboxes have cell links that yield TRUE (if selected) or FALSE if not selected - linked to cells A1, A2, A3, A4 respectively.

2. Option buttons - one for view and another one for print. Through the Format Control, the View and Print option buttons have cell links to cell A5 yielding 1 for View and 2 for Print.

3. Command button - that will clear the check boxes

Issue
I would like to know if there is a way to code in VBA to:

1. Unhide the spreadsheets corresponding to the check boxes if selected;

2. Print the spreadsheets corresponding to the check boxes if selected for printing; and

3. Clear the checked boxes to unchecked if the Command button is clicked.

I'm struggling with coding to perform the above tasks.

View Replies!   View Related
Refer To The Last Row In A Selected Area Using VBA..?
I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...

If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved