Controlling Cursor Movements
Sep 5, 2009
I have a workbook that contains spreadsheets that serve as input sheets to generate reports (other spreadsheeets within the workbook). I did not create user forms as I find them cumbersome to do so. Instead I formatted the input sheets to be extremely user friendly. I protected the sheets so that user can only access the unlocked cells that require inputs.
Question:
Is it possible to control the movement of the cursor by both tab and enter like is done in the user forms. The input cells are spread out in various locations on the spreadsheet - therefore, I would like to drive the direction of the cursor as to what cell to go next after an input is made and entered by either hitting the enter button or tab button.
View 9 Replies
ADVERTISEMENT
Apr 20, 2014
After I did the conditional formatting, I wanted the cursor to skip the colored cells. I used this code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Moves the active cell to the first column when enter button is pressed
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 12
Cells(Target.Row, Target.Column + 2).Select
Case Else
Exit Sub
End Select
But to no avail it applies to the whole sheet and I did this for "Condominium". How do I make it as a condition when I choose any property type? and then it'll go one step down to column A when the last row is typed/filled in?
For example, if I choose Condominium as my property type, Land Size (i.e. column M) is colored so the cursor will go from column L to Column N. If I choose Land as my property type, Furnished to Floors total (i.e. columns I to L) Bedrooms to Rooms Total (i.e Columns O to Q) Indoor Features to Other Features (i.e. Columns T to W) are colored so the cursor will go from column H to M then Column N to R and then Column S and then move one step below to column A when entered. and so on.
sample sheet.png
View 3 Replies
View Related
Sep 17, 2009
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 2 Replies
View Related
Feb 17, 2009
I am building an inventory application in 2007. I need to track the movements of the items so I was planning on a worksheet containing the item no, date, location.
Since an item will move around and thus have multiple records, how can I extract only the latest date record for each item and store them in another worksheet ?
So I would have 2 worksheets, a. transactions, and b. current inventory. Or am I trying to reinvent the wheel. I am sure there are a lot of inventory applications already written - how do I find one? So, 2 question!..
View 5 Replies
View Related
Aug 1, 2008
I have a spreadsheet that tries to model a possible stock price movement in a column. So in column A I have all dates starting from today and in Column B a specific stock's possible stock price movemens onver the next 2 years.
So dates start at A3 and stock prices start at B3 and end at A734 and B734.
This gives me ONE possible stock movement and to get the most accurate movement, I will need to model this 20,000 times.
Therefore, I want to create a loop macro that repeat this motion 20,000 times in the columns next to A and B. So which loop effect would I need to use and how would the code look like ? This is honestly my first time attempting to use a loop macro
View 9 Replies
View Related
Nov 29, 2006
I am using vba to control internet explorer and return the inner html. To do this I have something like:
Dim arrinnerhtml As Variant
arrinnerhtml = IE.document.body.innerhtml
My problem is that I can’t get the meta description and keywords tags. The title tag seems relatively easy using:
Dim Title As String
Title = ie.document.Title
Is there a relatively simple way to do this for the description and keywords tags? It’d be nice if it was ie.document.description although it doesn’t seem to be. I’m obviously missing a trick here.
View 9 Replies
View Related
Sep 10, 2009
Following up on a tread from Sailor64, I tried to use a code DonkeyOte created,
View 2 Replies
View Related
Jul 28, 2006
how to liberate only the sheets already opened by this user, even after he/she has closed the workbook? It means that when this user re-open the workbook, only the password for this step (workbook) and of course for the sheets still closed (not yet visited), will be requested...
There will be about 20 users, accessing the same file (at least 8 or 9 at the same time), and each one has a different level. For this reason the workbook is also protected: depending on the username and password, I'm trying let excel "knows" what sheet each user can open, without type a password again. trying to illustrating: (all users and password are added by me, using a MasterSheet)
user1 ----- already unprotected sheet9 ----- can open sheet1 to 9 (no password, even re-opening the workbook)
to open the sheet10, a username and a password are required
user2 ----- unprotected sheet3 ----- can open sheet1 to 3 (no password, even re-opening the workbook)
to open the sheet4, a username and a password are required
............. and so on.........
View 4 Replies
View Related
Mar 21, 2007
I have data in an Excel worksheet that needs to be entered into a proprietary programme. I believe this is possible using SendKeys but as I need to keep switching back to Excel to get the next bit of data I am unsure as to how to go about this. The program would already be open as it is a dial up situation and I would have to dial into the relevant site first.
View 4 Replies
View Related
Jan 29, 2014
I am working on an Excel macro which uses the Shell function to open another application, the AppActivate statement to change focus to that application, and then a series of SendKeys statements to perform tasks for which keystrokes (hotkeys) are available. However, there is one step in the process which does not have a hotkey available, but requires clicking on a drop-down with the mouse. Is there a way to have an Excel macro "click" on something for which a hotkey is not available?
View 1 Replies
View Related
Dec 31, 2009
I would like to do something similar to wiL with an employee drop down list. As the user begins to type the name, the drop down would narrow the choices alphabetically or the user could select the drop down list then hit the first letter of the name and go to that letter of the list (i.e. selecting "M" to go to the portion of the list that starts with "M").
View 2 Replies
View Related
Jul 17, 2009
I have a spreadsheet with ~30,000 rows of data that is using the "NOW" function. The endusers are having an issue with the spreadsheet having to recalculate everytime they run a filter as the spreadsheet recalculates the formulas everytime.
Is there a way to turn the autocalculate off for this spreadsheet only when it is opened?
I need the formulas to calculate once only when the sheet is initially opened but then turned off after that. When user closes the spreadsheet, the autocalculate needs to be turned back on.
View 3 Replies
View Related
Aug 14, 2009
I am working on an excel sheet (2007) with about 8 pivots from the same data (huge db). The 8 pivots take a different cuts of data. One filter element is same across all pivots. (For instance let us say that filter is City names: with 10 different city values in it- NY, London, Chicago etc)
What I would like to do is have a system where I just chose NY in one place and all the 8 pivots should use NY as one of the filters and refreash the data accordingly.
What I am doing now is I got to each pivot, choose NY first and then refreash all for the data.
View 14 Replies
View Related
Sep 7, 2009
How can I control non-Microsoft applications using VBA? For example, how can I open a pdf file, print it and then close Acrobat Reader?
View 5 Replies
View Related
Nov 18, 2013
I have 2 list boxes and want to controll scrool range (View) depending of selection of List Box 2.
Here is my code.
Private Sub UserForm_Initialize()
'ListBox1
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim arr
Dim I As Long
Set rngSource = Worksheets("Material").Range("F4:K3000")
[Code] ..........
View 8 Replies
View Related
Mar 27, 2009
In Excel 2007, I'm writing a macro to create several charts (column) that need to match what a designer has already built. I'm having a devil of a time finding the code I need to create parts of the chart. I've searched the web and this forum, but I must just not be asking the right things.
I can see everything I want to do in the format pop-up window when I right click on the charts in Excel, here is the path:
1) Format Minor Gridlines, Line Style, Dash Type, Rounded Dot
2) Format Axis, Axis Options, Major tick mark type, None
3) Format Axis, Axis Options, Position Axis, Between tick marks
View 9 Replies
View Related
Apr 26, 2006
I have several toggle buttons on a spreadsheet for the user to choose which month they want data for. Can anyone tell me how I can make the toggle button that is in the down position change to the up position and not execute the command again when one of the other toggle buttons is pushed?
View 6 Replies
View Related
Feb 27, 2007
Imagine a rectangular box, with a horizontal line across it, drawn using excel cells.
This is a representation of a screen that comes in 2 sections - sometimes the upper section is largest, sometimes the lower. (Sky above, land below for example)
Is there a way to draw and control the position of the line in this box, , so that by adding a numeric value in two adjecent cells, for example 75 25 would give more "sky" (the line towards the bottom) and 25 75 would give more "land" (The line towards the top)?
View 9 Replies
View Related
Jan 24, 2010
1) What function can I use to replace a specific cell in spreadsheet 2 with what I type in a specific cell in spreadsheet 1 (e.g. type red in spreadsheet 1 in A1 and it will replace a blank cell A1 in spreadsheet 2 with red)? I have around a hundred spreadsheets which I need to name by typing a name in cell A1. Rather than doing this, I already have the names of the spreadsheets in a column.
2) Is there anyway to control the attributes of cells in another spreadsheet? E.g. change to bold, red and specific size font in cell A1 in spreadsheet 2 using cell A1 in spreadsheet 1.
View 2 Replies
View Related
Jun 23, 2014
I have a userform created to enter clients information which by submitting goes to sheet1 ..
Now the challenge is i want to make sure that no duplicate entries are entered thru userform.
And if duplicate company name is entered then all the client details of the other fields of the userform shall display the values of previous entry so that i can edit the latest changes to the sheet.
View 3 Replies
View Related
Dec 15, 2008
A while back I was assisted with trying to prevent excessive screen flashing...
I was originally using the Application.ScreenUpdating=False method...but it was still a little jumpy... so Richard Schollar (a valued member in our forum) helped me with this code and it seemed to work...
Now I am trying to apply it again to another macro and it is not working so well. The thing I found is that on some people's computers it does work well, but not on mine.
Does anyone know why that is and how I can fix it so it doesn't flash on mine either?
View 14 Replies
View Related
Aug 13, 2009
how to fix my excel 2003 run-time error 438 "Object doesn't support this property or method" error? The VBE highlights this line of
View 4 Replies
View Related
Jun 11, 2013
The below code and variances of it has always worked for me when controling he pivot fields, however this no longer works in excel 2010 and i cannot seem to come up with a work around.
Code:
Sub Pivot_Date()
Application.ScreenUpdating = False
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterCell As String
[Code] .......
Its worth noting that this will work wen selecting all but not for individual fields. I have also tried skipping the loop ad simply setting the current page to the ilter cell but this doesn't work either.
View 1 Replies
View Related
Nov 2, 2007
I am trying to change the text that appears on a button on a worksheet via a macro. There is only one button on the worksheet so in the macro I use
MyCaption = activecell.value 'Pick up the button text from the spreadsheet
For each sh in activesheet.shapes
sh.caption = MyCaption
'I know this would do all, but only one button as I said
next sh
which generates "Object doesn't support this property or method".
This is strange because if you get the properties box up, Caption definitely shows as a property of a button control.
View 9 Replies
View Related
Dec 30, 2007
I have a simple macro that allows for inserting cells above the selected cell. It actually inserts 2 cells: above the selected one and above the one immediately to the right on the same row.
I need to add 2 features to prevent mess ups:
- The Active cell should only be on column "N" and after row 15 on the sheet. Any other cell selection should be ignored or ideally pop a warning to prompt user to select a > N15 cell.
- An OK/Cancel dialog box that pops up before execution and reads:
"Are you sure you want to skip "text_of_selected_cell" for date "text_of_Column-13_cell_on_same_row"?"
Sub SkipDayRelative()
ActiveCell.Offset(0, 0).Range("A1:B1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
View 9 Replies
View Related
Nov 17, 2003
I have a userform with 24 text and combo boxes. Aside from using the enter and exit events, is there an easy way to have the active text/combo box be highlighted in a color?
View 9 Replies
View Related
Jan 30, 2014
I have a spreadsheet that has a couple of columns that ask for "Move In Date" and the other "Move Out Date". These dates are used in other calculations so there can be only one or the other in each row. I have users that mistakenly either leave both blank or both popluated. Is there a way to stop them with a message telling them that a date needs to be entered or deleted, maybe with data validation?
View 1 Replies
View Related
Jan 28, 2010
I want to control 2 cells values from a dropdown list (linked to another worksheet). How is it possible?
I can do it with embedded ifs as long as the number of values is not too big, but I want to get more extended.
View 7 Replies
View Related
Apr 8, 2013
After organizing my data I now need to export to a simple txt file. But I need to control to final format better then the simple "save as" allows. What I need is a simple list of in a single column, separated by a carriage return. I am working with a "small" list of addresses, and by small I mean 27,188.
Every format I try ends up with quotation marks and a strange box like symbol. What I have is a full name and 2 line address per field in .xlsx format. I also have the data separated into fields by line.
Is there any way to better control the output when exporting to a .txt file?
View 2 Replies
View Related
Feb 27, 2012
I have a spread sheet whereby it contains:
- 12 unique values in column 6 which represent states within the country.
- 7 unique values in column 25 which represent the types of services.
- 5 unique values in column 27 which represent the days for doing the services.
I also have a user form that contains CheckBoxes equivalent to all above unique values, and I want the CheckBoxes to do the duty of a filter, whereby after selecting the needed CheckBoxes and clicking on a seperate botton "creating report", I want to show on the spread sheet only the rows of the values being checked and all other rows which don't contain the needed values should be either hidden or deleted.
I'm using Microsoft 2007 on Windows 7.
View 2 Replies
View Related
