VBA: Column Select then Data Select then return to cell A1
How do you write in VBA:-
to select a column then select the range of data held in the column (say B1)
then return to Cell a1. The data in the column can change in the workbook as
it is updated regulary, so the range of data in the column varies constantly.
There are column headings so the range in column B1 must start at B2.
It's not entirely clear what you want but maybe this which copies the used
range of column B and pastes it into A2
Dim LastRow As Long
Set sHt = Sheets("Sheet1")'Change to suit
LastRow = sH...Record Locking 03-31-10
Using Access 03=85
I=92ve been researching access record locking for about two days now and
I can=92t seem to find any solid advice.
I have a typical database (fe mde with linked access tables to be mdb)=97
users have their own fe. The db opens in shared mode, the default
record locking is No Locks, and the =91Open database using record-level
locking=92 is checked. With most of my forms, users will never access
another=92s record. But I have this one=85
It=92s my understanding that you actually set the record locks via a
form, which I did on a single...On a sheet selection as a web page how do you embed mp3
I have several sections of worksheets that gte published as web pages. I
owuld like to ember an mp3 into these pages so wehn they are being viewed
background music will play. How do you do it?
...Selecting cells with text and placing them in a column
Since combo boxes can only read continuous cells, I need to select al
the cells in a column that have text and copy them to a new column...
heres what im looking for
you may try the following macro which copies the selection without
blanks to the adjacent column
dim rng as range
dim cell as range
dim row_index as long
dim col_index as integer
set rng = selection
if rng.columns.count>1 then exit sub
col_index = rng.column+1
for each cell in rng
if cell.value<>"" then
cells(row_index,col_index).value = ...Auto Sort 1 column and select data
I have found many solutions that come very close to the requirements I need
for an auto sort and select. I like the formula posted on 1/13/2009. then
found issues if numbers stored as test. was able to overcome that issues by
adding INT the the original formula. I have a randon amount of data always in
35 columns. Once column f is different, it contains ids, unique and do not
repeat. I could have 6 rows of data or 3000 rows.
I populate this worksheet using an IF formula, and this formula is repeated
all 3000 rows for each column. IF(source <>"",source,"")
even...Select distinct e-mail adresses
I want to extract some data from 2 tables One of the tables contains an
email address. I want the query narrow down so that a single email
address is only output once. What SQL statement can I use to get this
e.g. data contains
Field1 Field 2 Field3 EMAIL
a c w2 email@example.com
a d w1 firstname.lastname@example.org
a c w5 email@example.com
I only want to output
a c w2 firstname.lastname@example.org OR a d w1 email@example.com
a c w5 firstname.lastname@example.org
Thanks in advance
How about something like this?
SELECT Field1, Field2, Field3, EMAIL FROM
(SELECT ROW_NUMBER() OVER (Par...Should not be allowed to close a case with open service activities
You try and close a case with an open service activity, case will allow you
to close it anyway.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...Owner-draw CListCtrl selection problem
I have a class named CMyListCtrl derived from CListCtrl using LVS_REPORT
with multiple columns. It is owner draw and overrides both OnPaint() and
DrawItem(). OnPaint() is called to draw a series of rectangles that create a
grid-like effect. Then it iterates through all items, populating a
DRAWITEMSTRUCT and calling DrawItem().
Selections are drawn using a distinct background color for the row (which
must be done in OnPaint() so as to preserve the grid effect) and a different
text color (done in DrawItem() ). This works great when the user selects any
item on the list--except the v...how to lock the textbox position in publish 2003.
i want to lock the textbox for size and position, just for change the text
This is not an option
"ttminh40" <email@example.com> wrote in message
>i want to lock the textbox for size and position, just for change the text
ttminh40 <firstname.lastname@example.org> was very recently heard
> i want to lock the textbox for size and position, just for change the
An often-suggested workaround is to create a large rectangle, zoom out to
the lowest possible zoom fac...Select all chart data points
I have made a graph in Excel 97, but I cannot select all data points on
the graph. When I click on a data point only every other point (or so)
I have deleted the graph and re-entered the data, still no good!
If I make a quick temorary graph by opening excel from scratch in a new
window this seems to work (click on a point and they all highlight).
Is there a default setting somewhere!?
What is it you want to do with the series once you select it?
"CL" <email@example.com> wrote in message
news:firstname.lastname@example.org...HELP!!! How to use macros to have one-click cell selections.
Hi. I posted this question before, but, because I don't understand all these
advanced Excel computer terms, it wasn't much help. ALSO I ONLY GOT ONE
RESPONSE. EVERYONE HELP!!!
MY QUESTION IS THIS:
How, in Excel, can I set a cell to contain text such as [Your Name], and,
when I single-click it, have the cell become selected in dark gray, and when
I begin typing, have the cell clear so I can type?
Basically, I'm asking because I've seen in some Microsoft Word templates
things/words like the cells I've just asked how to make. These words are
there so the user easily kn...When "Save As" is selected
Okay....if a user selects "Save As" from the menu, I
would like to display a message that states
"Save As is disabled!" I pretty much want to
prevent the "Save As" menu option from being selected.
Is this gonna be possible???
thank you everyone
Try this, Alt+F11 to open VB editor and double click 'ThisWorkbook' and
paste the code in on the right
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Save As isn't allowed"
Cancel = True
...ActiveExplorer.Selection exception / working with currently selecded items
I am trying to create an Outlook Add-in that will pick all selected contact
items in a currently visible folder and will create a broadcast fax message
using information saved with each contact item.
I was originally trying to perform this using VBA macro. It crashed and I
tried to make an add-in (for better stability) that currently gives me the
same error message.
When I do colSelectedItems = oApplication.ActiveExplorer.Selection, Outlook
starts to pull each selected contact's data into Selection collection. It
does it for about 30sec (memory used by application ...Only Allowing Certain Codes In Column
In my spreadsheet, I would like to set a column so that it will only allow
certain codes. For example: I would like only the following written exactly
as is here to be allowed to be entered into a column:
How can I set the column to only allow these & can I make it so it will
display the codes for the user.
Select your column and then Data>Data Validation. Take the list option and
enter your list separated by commas and then enter any input or error
message you want
Microsoft MVP - Excel
I've set up a bunch of custom categories and deleted a bunch of the
ones that were provided with Outlook 2002. Now I'd like to have them
locked down so that only I, as administrator, can change them. I'd
also like the category to auto-fill when I start typing its name; or
in the alternative, to have the category checklist popup for me to
How could I accomplish this?
Crabby, but Great Legs!
...Select First(Field1) From Table1 Order By Field1
I am trying to run a query in this format: -
SELECT First([FM_InvoiceImport].[Field1]) AS FirstOfField1
FROM FM_InvoiceImport ORDER BY [FM_InvoiceImport].[Field1];
It keeps telling me 'You tried to execute a query that does not
include the specified expression '[FM_InvoiceImport].[Field1]' as part
of an aggregate function.'
Why won't it work??
SELECT First(FM_InvoiceImport.Field1) AS FirstOfField1
SELECT First(FM_InvoiceImport.Field1) AS FirstOfField1,
GROUP BY FM_InvoiceImport.ID...Locking a file
Is there a way to lock a file so that it can not be copied?
Almost any suggestion you're going to get would rely on macros and macros can be
Heck, anyone could even avoid excel and just copy using windows explorer.
> Is there a way to lock a file so that it can not be copied?
Short answer: No.
Excel's protection is not security, just protection against changes to the
layout, format and some content. Any password protection you assign to a
workbook and/or worksheets can be bypassed in a matter of minutes ...Allowing View Only Admins ability to modify SMTP addresses
I need to allow View Only Admins the ability to modify SMTP addresses for
Users and Public Folders. By default they can not. Anyone know what
permissions are needed to enable this? Running Exchange 2003 Enterprise SP2.
This might point you in the right direction:
"jkc" <email@example.com> wrote in message
>I need to allow View Only Admins the ability to modify SMTP addres...Multifile select in FileOpen dialog
Hello everyone. My app (SDI) requires several different
data files to be loaded, after which it does some serious
number crunching. It would be best if, rather than the
user having to open each file individually, they can
select several files at once in the standard OpenFile
dialog. Is this possible, and if so where do I go for more
info? Thanks in advance!
Since this is an MFC newsgroup, I'll assume you are using MFC and recommend
that you use the CFileDialog class.
Just set the OFN_ALLOWMULTISELECT flag in the OPENFILENAME structure (in
CFileDialog::m_ofn). The documentation for ...IN SELECT Query Criteria
I think I read somewhere that using an "IN SELECT..." type of query will
speed up your query performance in Access 2003 better than linking the two
tables by their common field. Is that correct? If so, what is the proper
syntax for creating this type of query criteria?
My scenario is that I have a very long list (1000's) of employees and a
select group of managers (about 30). I only want the employees whose manager
is in my table of selected managers. I should end up with a list of <200.
Is this a good way to use the "IN Select.." as my criteria?
...Need help with the SELECT statement. 12-04-09
I need to give a total count of LCPI from another table based upon the
Loanid. Below show the
business rule and desire results. Thank you in advance.
IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
DROP TABLE #LoanInfo
CREATE TABLE #LoanInfo
LoanId VARCHAR(8) NULL,
Status VARCHAR(10) NULL
IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
DROP TABLE #LoanComp
CREATE TABLE #LoanComp
LCPI INT NULL,
LoanId VARCHAR(8) NULL
INSERT INTO #LoanI...windows xp, can not select multiple users in lusrmgr.msc
Hello from Spain,
i was studying lusrmgr.msc, the console for the management of local users
and groups, for my mcdst certification when i realized i couldn't perform
the action of selecting some users with the shift key neither with the
control key. In the book they say it's possible... what am i doing wrong?
I'm working on a windows XP Pro 2002 service pack 3.
On Wed, 18 Aug 2010 20:35:58 +0200, "m0rg4n" <firstname.lastname@example.org>
>Hello from Spain,
>i was studying lusrmgr.msc, the console for the management of loc...How to make select B subquery on select A?
My terminology is probably wrong but I'm working with a simple form that
lets user enter data about customer invoices (errors etc).
I have 3 tables (customer info, invoice info, invoice error log).
Now, in one field I want to enter customer Id (from "customer table") and
then I would like the second field to display a list with that particular
customer id's invoices (found in "invoice table").
So given input in "customer text box" the "invoice text box" should display
a select list of invoices (that I can pick from) that belongs to th...Go back to last selected Worksheet
Any keyboard shortcut/Menu Item to go back to last selected Worksheet/
tab in excel workbook. e.g. Excel having four tabs/sheets a b c d, I
moved to d from b, and i want to go back to b. I have a Workbook with
20+ sheets/Tabs inside it.
Avi, did you end up getting an answer on this? I am having the same problem.
> On Thursday, June 24, 2010 5:25 AM Avi wrote:
> Any keyboard shortcut/Menu Item to go back to last selected Worksheet/
> tab in excel workbook. e.g. Excel having four tabs/sheets a b c d, I
> moved to d from b, and ...Select dates in range
Good day to all,
I've a table with some records of events for subjects and on certain dates,
now I need to get a query that returns me the Subject, Subject creation date,
3 months after creation date, Value at 3 months, 6 months after creation
date, Value at 6 months:
This is for example:
ID Name Born
1 Jon 6/6/95
2 Betty 8/12/95
3 Rudolf 15/01/96
Date subjectID Wheight
6/7/95 1 10
7/8/95 1 18
5/9/95 1 25