Search Selection With Scroll Area Set

Sep 14, 2013

I have a spreadsheet form with a bunch of cells receiving and writing data via vba. On the form is a search box. The data is on the same sheet as the form to prevent flickering by activating another sheet. The problem is that I cannot select the search area and set the scrollarea or the "Find" quits after the first find:

if I have 2 jacks then it will find the first and not the second.

If I don't set the scrollarea of course it goes to the selected cell and moves the form out of the picture.

If I select a cell in the form area after the search, the "Find" is reset to the beginning and locates the same cell again.

Here is the code:

Code:

On Error GoTo DataNotFound
Cells.find(What:=txtFindText, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate

[Code]....

View 9 Replies


ADVERTISEMENT

Scroll Area Set But Won't Stay Put?

Oct 29, 2007

I set the scroll area on a worksheet and saved the file. The next time I open the worksheet the scroll area is no longer set.

View 7 Replies View Related

Locking A Large Scroll Area

Feb 28, 2007

Can a scroll area be locked at any size using VBA code? I am able to lock area A1:J20 in the Private Module of a worksheet but nothing larger.

View 6 Replies View Related

Lock & Limit Scroll Area

Jun 16, 2008

I want users to be able to scroll top-down or sideways up to a certain point only.
What is the VBA code to accomplish this? Let's say for example I want to limit scrolling from A to L rows 1 to 50 only.

View 2 Replies View Related

Scroll Area Not Saving When Workbook Closed

Nov 15, 2006

Ive tried disabling scroll on sheet 1 of my workbook by entering the cell range into the properties window in VBA. However when I save the sheet and reopen it, it seems to reset and are able to scroll again.

I tried using the following:

Private Sub Worksheet_Activate()
Worksheets(1).ScrollArea = "A1:Q51"

End Sub

and a few variations of the above but this dosnt seem to be working. The only other code I have in the workbook is under "thisworkboko" which is this:

Private Sub Workbook_Open()
Application.DisplayFullScreen = True

End Sub

View 5 Replies View Related

Scroll Area And Hiding Unused Portions For Entire Workbook

Jun 27, 2012

I use all of column A, B, and C, but i only use D1 to H44 other than that. Is it possible to hide everything to the right of Column I and below D44:H44, but leave the scroll area for columns A B C able to scroll to the bottom?

View 6 Replies View Related

Limit Scroll Area - Code Activates Only On Mouse Or Keyboard Input

Apr 21, 2007

I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.

How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
Range("A1").Activate
End Sub

I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?

View 2 Replies View Related

VBA Search And Scroll

Nov 15, 2007

I have a document that is rather simple but has 32,984 Rows.

What I would like to do is program the sheet so if I enter a six (6) digit employee number in A2 (A1 is a header) then all the rows would scoll up and stop just below A2 in other words strop at row 3, if this makles any sense,

Also what I woudl like to do is the same option in A2 and have the same effect but here the user would have to enter only the first 3 characters of a name and then the system would scroll through all the options and start listing them as above starting at row 3.


The end result for both options would be to highlight the desired sorted rows..............

View 9 Replies View Related

Search Along A Column And Scroll To It

Oct 10, 2008

Is there a way using VBA, that I could type something in a cell, and then it would find it in the A column and scroll to it?

View 3 Replies View Related

Macro To Select An Area, And Print Selection

Mar 15, 2009

I need a macro to select the area within the blue invoice sheet box, so everything within the blue invoice sheet box is selected , and then the selection should be printed. I will have hundreds of these invoice slips made, right below each other, i was wondering once this is done, is there an easier way to to have each invoice selected, instead of making a custom button for each sheet ( which selects just that invoice , and prints that selection ).

( Column m through v, starting at row 2 stoping at row 68, is the selection required in this example, everythign within the blue box. ).

View 13 Replies View Related

Macro To Search For Text And Paste Data In Other Area?

Apr 1, 2014

I have a report that runs, and it shows users that are in different groups. Within each group are primary users, and sometimes secondary users. Attached is a spreadsheet showing an example of the report in "A7:A52"

What I want to do is have a macro scan column A, and if a group is found, create what I have made in the rest of the spreadsheet (CD6 and below). I manually filled in the data for group 1, and would like it to continue off to the right in the same format.

There are some areas that dont have groups, such as there might be group 1, group 2, and then nothing till group 10 (as shown in the example)

View 12 Replies View Related

Search Selection To See If Any Cells > 0

Dec 9, 2008

I have selected a range in a worksheet using VBA. How can I now code so that if any cell in the selection has a value greater than 0 , then

Put up a Message. I do not know visually what has been selected, and it will change from sheet to sheet so I cant hard code the range.

If all cells have a value of nothing or zero, I will delete the entire selection.
If any row has cells with a value > 0, I will keep it and move to the next row
Then, delete all rows where all cells have a value of nothing or 0

View 9 Replies View Related

Restricted Scroll Areas Won't Scroll

Nov 26, 2007

I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)

View 2 Replies View Related

Control Scroll Bars: Remove The Scroll Bars From Just One Worksheet But When Unticked

Jan 13, 2007

In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?

View 2 Replies View Related

Print 2 Selected Area (highlighted Area)?

Apr 11, 2013

How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time

View 7 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

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 2 Replies View Related

Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart

Nov 1, 2006

I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:

1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.

2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.

3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).

What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.

View 3 Replies View Related

How To Set Fixed Print Area In Excel So Cells Within Print Area Can Still Be Modified

Feb 12, 2013

I have a workbook with multiple sheets that I need to print.

Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?

I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.

View 1 Replies View Related

Displaying Input Box Based On User Selection Of Dropdown Selection?

Jun 3, 2013

If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.

View 1 Replies View Related

Increment Cell On Selection And Lock/Protect From Re-Selection

Oct 3, 2006

I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:

1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.

I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................

View 2 Replies View Related

Copy & Paste Selection Based On Combo Box Selection

Jan 30, 2007

I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-

If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.

View 9 Replies View Related

Disable Selection In Listbox On Selection Of Item

Apr 4, 2013

I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.

Is there any way to do that as I tried

VB : Listbox.enabled = False
and
VB : Listbox.locket = True

in Listbox - Click/Change but no desirable results got.

View 1 Replies View Related

Restrict Drop-Down Selection Based On Selection From Another

Jul 20, 2007

I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.

e.g. In cell C115 I want to have the value BDS05.

I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.

I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).

View 6 Replies View Related

Run Selection Change Event Only If The Row Selection Changes

Mar 26, 2007

I'm using the following code in a worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
col = ActiveCell.Column
Range("output") = ActiveCell.Offset(0, -(col - 4)).Value
End Sub

In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output".
The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column.
I'm sure there's a straightforward way to reprogram this.

View 7 Replies View Related

Investment Selection With Cell Selection

May 4, 2007

I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.

There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]

[ H ] is price of the leverage

[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain

Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.

What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.

Let say, the balance is 10000.

If I enter 2 at cell B. Then, it will calculate the value for cell C & D.

If I enter 750 at cell C, then it will calculate value for cell B & D.

If I enter 25 at cell D, it will calculate value for cell B & C.

how to use OR Selection in excel.

View 9 Replies View Related

Scroll Box Dirrectly

Nov 19, 2009

I am trying to make a scroll box so I can enter data into the scroll box dirrectly. Such a simple thing.

View 11 Replies View Related

Can't Scroll Down On Excel

May 13, 2013

I'm trying to scroll down my work sheet but it's not letting me. Every time I move the scroll bar down the page it just keeps showing me the same part of the sheet eg cells 1-30 when I want to see all the way down to the bottom. I have some 20,000 lines I need to scroll through but I can't see all of them. To add to this, I keep having lines hide between other lines so theres a big gap like going from line 120 to line 4000 and I have to manually unhide them every time to see them.

View 2 Replies View Related

List Box Scroll Bar

Nov 14, 2008

Does anybody know how to force the scroll bar on a listbox to be at the bottom rather than the top when filled with data?

View 6 Replies View Related

Can't Scroll Back To Top

Dec 26, 2008

I have a spreadsheet with some scripting that automatically emails the report to whoever is on the sheet. I'm not sure what kind of scripting it is. I think my boss found it somewhere.

Whenever I scroll either down or up, the first 3 rows disappear and I'm not able to scroll back up. The scrollbar shows that I'm not at the top, but it won't let me go back up. The first 3 rows contain the button to send the email. If I close out without saving and open it again, its fine, until I scroll up or down.

View 4 Replies View Related

Percentage Value In Scroll Bar

Aug 23, 2009

I've been trying to set the scroll bar min/max/small change to a decimal (percentage value) within the properties, obviously this does not work.

Since users can enter a value in the link cell for the scroll bar, how can I modify the current min/max/small change via macros so the scroll bar will show a min on 1%, small change of 0.25% and max of 25%?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved