Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




In Excel, Select Whole Row, Run Macro, End By Selecting Original Row Automatically


I have an Excel spreadsheet macro which takes off the auto-filter and then after carrying out various functions, puts the auto-filter back on again. The functions in the macro need the whole of one row to be chosen manually before running the macro. I want to end the macro by automatically highlighting the whole of the row that was orignally chosen. I assume I would need to save the address of the original ActiveCell/Row, say as a variable, and after carrying out the various functions, return to the saved address, ie: the original ActiveCell/Row. Can you give some guidance please.

 




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Selecting First And Last Rows Excel Macro
The second part to a post that I made early involves creating a macro that will select the first and last date/time stamps from a Excel log file. In colum A there are employee names. In column B there are date/time stamps from the event log on the file server. It looks something like this



What I need the macro to do is copy the first time and last time for each employee and paste those two values to column D without any blank rows in between. So from this screen shot column D would be:

6/11/2007 8:54:48 AM
6/11/2007 8:28:03 AM
6/11/2007 8:42:36 AM
6/11/2007 8:40.02 AM
6/11/2007 8:33:27 AM
6/11/2007 8:29:39 AM

Any help would be great!

VB Macro - Selecting Multiple Columns From Excel Worksheet
I have the following bit of code that I need to expand to select multiple columns (rather than one) from an excel spreadsheet I am working in. Right now, the code is only selecting data from column B, whereas I want to select for columns B -F. I have tried the obvious things (adding "B", "C", etc) - I haven't came up with a solution that will pull the data. I'm sure I'm missing something easy - Any help on this would be greatly appreciated.

'--- Set up the Outlook objects.
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

'--- Declare global variables to be used in each subroutine.
Dim DataOwnerAddress As String
Dim DataOwnerMessage As String

Sub MailItNow()

'--- Declare variables
Dim X As Integer
Dim TempDataOwnerAddress As String

'--- Prevent screen redraws until the macro is finished.
Application.ScreenUpdating = False

'--- Sort the addresses and names alphabetically, by the e-mail address.
'--- This is REQUIRED to prevent any duplicate addresses from
' receiving more than one e-mail.

Columns("A:F").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'--- Sets which row to start searching for e-mail addresses and names.
X = 2

'--- Begin looping through all the e-mail addresses in column A until
' a blank cell is hit.
While Range("A" & X).Text <> ""

'--- These variables will be used to search for duplicates.
DataOwnerAddress = Range("A" & X).Text
TempDataOwnerAddress = DataOwnerAddress

'--- Increment X until a different e-mail address is found.
While TempDataOwnerAddress = DataOwnerAddress
X = X + 1
DataOwnerAddress = Range("A" & X).Text
Wend

'--- Add the e-mail address to a global variable.
DataOwnerAddress = Range("A" & X - 1).Text
'--- Add a message with the user's name to the e-mail.
'--- Customize your own message and closing here.
DataOwnerMessage = Range("B" & X - 1).Text _
& vbCrLf & vbCrLf _
& "Please review the data listed above."

'--- Run the subroutine to send the message.
Call SendMessage

Wend

How To Select A Radio Button With An Excel Macro?
I have a worksheet that does a calculation and a macro to reset the worksheet after each calculation. One of the options on the worksheet is a radio button where you select either Residential or Business. I'd like the macro that resets the worksheet to select Residential when it is run, but I have not been able to find out how to do this.

I have found that I can use Application.Run to run the macro associated with the button, but this doesn't make the button appear to be selected.

Can somebody please tell me how to select this radio button within a macro?

Thank you for your time.

VB Macro For Printing Select Worksheets In Excel
Hi there

I've got an Excel Workbook with multiple Sheets. I have a macro for printing most of the Worksheets but most of the time only a third of them hold any data and I just end up wasting a lot of paper and ink.
Does anyone have a script, or extract of, that will only print a sheet if data has been entered in one or more cells on that sheet?
Before I just printed off the sheets I needed manually but most of my colleagues ouldn't quite get the hang of that.

Any help greatly appreciated

Dan

Excel Macro - How To Select Used Cells In A Column?
Hello!
solutin is in the last post

I'm trying to workaround this problem: http://support.microsoft.com/defaul...kb;en-us;815277

this changes data in a selection to make it REALLY of text type, provided the cells were already text type.

If anyone knows another way to achieve the same result, that is welcome too. (convert mixed numeric and text data in the same column to only text data).

This is NOT achieved by going to Format -> Cells -> text

Although this is a required previous step for the method in the link above. The numbers are only stored as text once a space is entered before the number in the cell. We can delete the space later and the number will remain stored as text.

So,
I need a macro to do the following: select used cells in a specific column.
I need to select column "values" but not the entire column, only the used rows (it is possible that there are empty cells in this column, that should not matter, I need to select the cells if there is a corresponding non-empty cell in column "dt")

for example:

Code:


column a | column b | dt | column x | values | column z
....a....|....b.....|.1..|....x.....|...123..|....z....
....a....|....b.....|.2..|....x.....|........|....z....
....a....|....b.....|.3..|....x.....|...456..|....z....
....a....|....b.....|.4..|....x.....|........|....z....


here i would need to select 5 cells from column values, counting title cell






I already know that changing data type is achieved with:

Code:
Selection.NumberFormat = "@"


all I need now is select the cells I want to change...

Hope anyone can help me
Cheers

How Do I Select IE Option Element With No Name, Value, Or Title In An Excel Macro
Hi!

I'm trying to automate webform from an Excel macro using values from Excel. I am running into a problem with a particular part that uses a Select element and several option elements that I am unsure of how to access in the macro. I know I can use the .selectedIndex property to set the option on the page but I need to be able to "read" the text display of the options into a string to compare with a value on the spreadsheet. I do not have the ability to modify the html code. I have posted the html code and the current macro code. I would be ever grateful for any assistance you all could provide.
HTML:

<select size="1" name="equipmentDescription" style="position: relative; width: 357; height: 23">
<option> Item 1 </option>
<option> Item 2</option>
<option> Item 3</option>
<option> Item 4</option>
</select>

VBA:

App.document.all("equipmentDescription").selectedIndex = (A # between 0 - 115)

The vba code will change the selection to the item but I need to get the text of each item to determine the number correctly because the options list changes daily.

Thanks for all your help in advance.

Keeping Original Formula After Macro Has Run
hey all,
this site has been great for me always so Im hoping I can get some help with my current problem.Its hard to explain but hopefully some kind soul can help.At the moment Im looping through columns and transferring my data values to another sheet.This is working fine.

What I want to do now,is have code within my macro that as Im looping, multiply my cell values by another cell value - then transfer my data values.I can do this ok but what Im having trouble with is not overwriting the formula that originally in my cells.

so Im thinking within my macro I need something like



Code:
fSheet.Cells(mRow, iCol).Formula = fSheet.Cells(mRow, 4).Formula & "*" & fSheet.Cells(mRow, iCol).Formula
as in set the formula = {the value I want to multiply by(which is given by the formula fSheet.Cells(mRow, 4)) * the original formula}


then at the end of my loop reset the formula to what it is originally.


sorry if this is confusing.please ask if you have further questions.
thanks.

How To Create Excel Multi-select List Box To Work In A Macro?
Hi
I need to create an excel multi-select list box on a worksheet, so that the user can make multiple selections. The examples that I found from the help index are meant for activeX list box (which is not what I want).
My questions are : (1) how to create an excel (multi-select) list box to work in an excel macro? (2) When the user to click on the right mouse button, the multiple selections will be transferred to another excel list box. How do I write code to do this? Thank you.

Selecting Text Automatically
what is the best way to automatically select (hightlight) text in a text box when the focus is set to that box?

Automatically Selecting Last Row In MS Flexigrid
Hi all,

I need some help regarding MS Flexigrid. I have a grid that has 3 rows and 4 columns. However, whenever, I click outside the 3 rows but within the MS Flexigrid control, the application will automatically select the last row of the MS Flexigrid. I want to be able to disable that. Is that possible?

Automatically Selecting Text In RichTextBox
I want to search in a RichTextBox for a specific word, then set that word to a specific color. The problem is that I need to find a way to select that text to be able to change that color.
This is what I have so far:


Code:
Dim var As Long
Dim sText as String
var = RichTextBox1.Find(sText)
If var <> -1 Then
RichTextBox1.SelColor = vbRed
End If

That obviously doesn't work. I need it to select the text, then I can set that selected text to the color Red. Not sure how to do this. Any ideas?

Automatically Selecting 1st Item In A DBCombo
I was wondering if anyone can tell me how to automatically select the first
item in a DBCombo list by default.

The DBCombo style I am using is: Type 2 dbcDropdownList

thanks in advance

Automatically Selecting An Item In ListView VB6
I've just changed over a set of information I had from a tabbed listbox to a listview because of it's ease of resizing use etc. However - I need to be able to automatically highlight an item in the list from the code. I used to be able to do this in the listbox just by the Obj.ListIndex = [number] syntax - but I can't find the property to do it. Here's what I'm doing to clear all of the selected ones in the ListView and then setting the one I want :

Public Sub SelectItem(Item As Integer)
If Item < -1 Then Item = -1
If DiaryList.ListItems.Count <> 0 Then
For a = 1 To DiaryList.ListItems.Count
DiaryList.ListItems(a).Selected = False
Next a
If Item <> -1 Then DiaryList.ListItems(Item).Selected = True
End If
End Sub

Obviously what i'm doing here is if I want to clear all Items from being Selected I call the Sub with -1 - however if I call it with an integer - I want it to clear all the others and highlight THAT item. This sets the properties right - I've checked in the Watch - but unfortunately does not show the item as being highlighted/selected in the on screen ListView. I've even tried calling DiaryList.Refresh at the end of the sub to no avail.

Any ideas?
Thanks
Adam Hourigan

Automatically Selecting An Option From A Popup
Hi, we're trying to create a small application to remotely (and automatically) uninstall an applictaion. We've got it to the point where we can run the unintall program but the uninstall prompts the user "Do you want to uninstall this applictaion". We need to either get round this or find a way so the "yes" button is automatically selected.

Any ideas??

Paul

Automatically Selecting Text Inside Textbox
I have a series of text boxes that the user can skip between by using the enter key, the user inputs data into these text boxes. The textboxes have a default value that explains their function, and I would like the data inside the text box to be 'hightlighted' when the focus is shifted so when the user presses a key the default data is replaced with the new data...

How can I automatically select the data in the textbox?

Automatically Selecting Text In A ComboBox Dropdown List
I created a combo box with the Dropdown List style property because I essentially want to restrict the user to enter only specified items.

I use the following code to select specific text in a combo box but it seems like there is a better way. This method is slow and you can see the combo box scroll through the items on a slow computer. Any help is appreciated. Thanks in advance for you time!


Code:

Public Function GetPC(ByVal strText As String) As Integer

Dim i As Integer

With frmMain.cboPriority
For i = 0 To .ListCount
.ListIndex = i
If .Text = strText Then
GetPC = i
Exit Function
End If
Next i
End With

End Function



I use the following code to call this function...


Code:

cboPriority.ListIndex = GetPC(rstAccounts!PC)



I tried to write to the .Text property of the list box hoping that it would select the item matching the text I specify but it gives me a run-time error. I found that I can only read the .Text propery at run-time when the Dropdown list style is used.

Thanks,
Luke

Automatically Selecting The Correct Printer (print Driver)
How do you automatically select a defined printer before you perform a "printreport" ?

For example:

If I have a sales report that I always want printed on a printer called "Printronix P5000"... How would I programatically select that printer for the report ?


Thanks in advance for any help !!!!

Running A Macro Automatically
I have a spreadsheet in excel 97 with a dropdown box. Can I automatically start a macro once someone has chosen from the dropdown box?

Automatically Enable Macro
Is there any way to automatically "enable macros" when a file is opened, despite the user's security settings?

Thanks!

Run Macro Automatically Every 5 Mins
hi,

I am looking to write some code to allow a macro to be run every 5 mins on my excel spreadsheet. I have tried using the following:

Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "MyMacro"
End Sub

Sub MyMacro()
Application.OnTime
TimeValue("15:00:00"), "MyMacro"'YOUR CODEEnd Sub

but this does not work. Please could somebody help or show me an example of a sheet that actually works.

Thanks in advance.

Execute Macro To Run Automatically
Hi,

I have created a macro that i want to automatically execute everyday. But for some reason when i run the below code to execute that macro it does not work. My macro name is imanimport. What i want is to open a excel spread sheet and run that macro that would create my output file. Please help????


Dim XL As Object
Set XL = CreateObject("excel.Application")
With XL.Application
XL.Visible = True
XL.Run "imanimport"
XL.Quit
End With

Set XL = Nothing

Need Path Of Original Excel Template.
Is it possible to grab the path and filename of an original Excel template file before it is renamed "Report1", etc.?

Automatically Print On Opening Macro
Hello

I am trying to build a macro to print automatically when the document is opened.

can anyone tell me how to do this?

I have tried creating a new macro in a template and put this code in:

<vbcode>
Sub pr()
'
' pr Macro
' Macro created 28/08/2003 by adavies
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0

End Sub
</vbcode>

That is in a macro in the document.

When I open the document, it does not automatically print.

Can someone help?

Macro To Execture Automatically At Startup?
Hi,

Is there a way to get a macro to exectute at the moment an Excel spreadsheet is loaded?



Cheers,

Rob.


Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

How To Add The Macro Automatically In Outlook 2000?
I have made a macro in outlook 2000 that will filter the emails and send a specific email (according to the subject) to a folder in the hard disk, and it works fine.

what I need is how to make a program that will (install) or add this macro to the outlook 2000 automatically without the manual work (I mean without making a new macro inserting the code and changing the security to medium..)..

please I spent weeks trying to solve this problem which I think easy but could not reach the solution yet ..

please any comment maight help!

Automatically Run Macro In Access Database?
Hi,

I have an Access database named db1.mdb in my C: root that has a Macro named Macro1.

How would I get Windows 95 to automatically run Macro1 every hour using the System Agent? Would I need to create a batch file first and call that every hour? If so, what would the batch file look like?

Any help and examples would be appreciated..

Dan

How To Use VBA To Automatically Update Macro In Another Workbook
Hi,
I am currently writing some VBA script for excel, due to time constraint, I have to release the early version of utility to the users, all together there are more than 6 copies of them distributed out.

Question,
1) For every change of new revision, I have to copy the latest macro to the users workbook manually, it is not a present experience of manual updating. Is there a way to automatically delete the old macro module then add the latest module onto the workbook without overwriting the data on the workbook.

I have tried using the macro recorder to extra the code out..but no code is generated.


2) How to enforce user select macro running option while excel startup. If user do not select macro running, is there a way to detect it.


thanks alot
regards
kaileong

How To Automatically Overwrite Files W/VB Macro?
Could someone help me out with automatically overwriting files when using a
MS Project VB macro? As you can see, I have the file saving to native
format, then to a HTML file. When it goes to save the HTML, it always gives
me an overwrite verification msg. for which I have to click "OK." I'd like
to avoid having to click and just have it overwrite. Here's the pertinent
portion of the macro:

FileSave
FileSaveAs Name:="K:ProjectsRetire CKP.html", FormatID:="MSProject.HTML",
map:="Jeff Main"

Thanks!

Need Help With Calling Macro Automatically &lt;RESOLVED&gt;
I'm currently using vb in excel to do several tasks, although one troubles me. I would greatly appreciate some sort of insight in this matter. Im trying to print the spreadsheet at midnight. The printing is no problem, i just call on a macro to do that. The issue arises with how to call that macro at midnight. Ive tried several ways including an ondata command, but have found no way to check the time, to call the macro automatically from either the spreadsheet or a command. Any help would greatly be appreciated....





Edited by - flopro on 6/14/2004 2:18:07 PM

VB6/Excel - Go Back To Original Worksheet After Copy.
Hello everyone,
I copied/create a new worksheet from my existing worksheet(sheet1). How can I go back to the original sheet after the copy function?

This is my code:

Code:
sheets(1).Copy After:=sheets(1)
How can I go back to sheets(1) and manipulate it, because there are some values in sheets(1) that I need to delete.

Thanks.
tgif

Close Original Excel File- Should Be Simple
Hello all...    

I have an application that opens an excel file (template), renames it and then saves the new file in a set directory. Does anyone know how I can close the original template file and leave the renamed one open?

Thnx,

Johnny V

VB6/Excel - Go Back To Original Worksheet After Copy.
Hello everyone,
I copied/create a new worksheet from my existing worksheet(sheet1). How can I go back to the original sheet after the copy function?

This is my code:

Code:
sheets(1).Copy After:=sheets(1)


How can I go back to sheets(1) and manipulate it, because there are some values in sheets(1) that I need to delete.

Thanks.
Giftx.

Automatically Load A Macro When A Workbook Opens
Hi all,

I've been trying to figure a way to run a macro everytime _ANY_ workbook is open. So I've tried these ways and they all fail:
1. Workbook_Open - This can be done only for a single workbook and I need the macro to be run on every workbook which is opened
2. Personal.xls - The macro has to be launched manually from the menus (Auto_Open doesn't work since there is on worksheet open at the time of execution of the procedure)
3. I've tried creating an Add-In but I couldn't see any macros displayed

So any hints are greatly appreciated. I just need to automatically execute a macro everytime a workbook is opened. Thanks a lot!

Automatically Start Macro Recorder In Word
Hello,

it is possible to start the macro recorder in word from an external vb-application?
We want to automate the creation of word-macros, so that we start the recorder
and the user can do what he wants and after closing the recorder, we want to
save the new macro in our application.

It is possible to do this?

Thanks,
Joerg

Selecting Tabs In Macro
Hope someone can help.
Created macro for a master workbook to select all but 1 sheet to be printed. Problem is that sheet tabs have specific names (1 for each employee) that I set to print out as the header & sheets are added & deleted periodically as employees come & go. Is there a way to select all sheets no matter what they are named?

Please answer in layman's terms or feel free to post correct code.

See my code below:

Sub Print_all()
'
' Print_all Macro
' Macro recorded 3/12/2008 by 03242957
'
' Keyboard Shortcut: Ctrl+p
'
Sheets(Array("Building Detail", "Building Scorecard", "005", "024", "065", "107", _
"110", "114", "124", "136", "138", "154", "155", "164", "168", "171", "195", "196", "197", _
"202", "204", "205", "206", "207", "208")).Select
Sheets("Building Scorecard").Activate
Sheets(Array("Sheet5", "Sheet4", "Sheet3", "Sheet2", "Sheet1")).Select Replace:= _
False
Sheets(Array("Building Scorecard", "005", "024", "065", "107", "110", "114", "124", _
"136", "138", "154", "155", "164", "168", "171", "195", "196", "197", "202", "204", "205", _
"206", "207", "208", "Sheet5")).Select
Sheets("Building Scorecard").Activate
Sheets(Array("Sheet4", "Sheet3", "Sheet2", "Sheet1")).Select Replace:=False
Application.ActivePrinter = "\peppsa00168PEPPRT02861 on Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\peppsa00168PEPPRT02861 on Ne04:", Collate:=True
End Sub

Thanks in advance

Automatically Select Highest Value
Hi folks!
Simple question, hopefully also with a simple answer..

I have four labels, all filled with a different date value (22.03.04 09:38:27)
How can I easily make my program pick the highest value / newest date??

Thanks in advance..

Automatically Select Tabs
does anyone know if/how i can automaticallt select tabs on an sstab control?
thanks

Automatically Select Recordsource?
I have about 3 textboxes bound to my database. Its obvious that when you press the left and right buttons on the db, that the textboxes change. How can I get the database to automatically change to the value I want.(automatically go right 3 times, ect without having to press it.) Thanks

Saving Excel Workbook With New Filename Without Closing Original?
Without confusing you all with everything the workbook does I basically need to find a way in Excel VBA of saving the open workbook with a different name without using SaveAs as this closes the original workbook and I need to do this for 20 new filenames within the same macro.

The original workbook has lots of formulas and the macro deletes a lot of these before saving as a new file. I need to save the file before I remove all the formulas and then save and close the new file. After this the original workbook macro loops and repeats this process for 20 odd files.

If there is a way of opening a new workbook and copying everything from the first workbook (about 10 worksheets) across then I can do this as SaveAs will not close the original workbook.

Alternatively if there is a simple way of saving the current workbook with a new name without using SaveAs then this would be ideal.

Thanks

Richard



Edited by - rickp101 on 1/12/2004 8:36:35 AM

Word Macro That Will Replace Single Characters Automatically...
... hi all. great forum you have here.
was wondering if someone could help me out... I don't know much about macro's, but I need to somehow create one that will automatically find & replace certain characters in .txt or .doc files.

we always receive documents that contain both French and German alphabetical characters. ie, ņ, Ń, ¬, …, ň etc etc. we need to make a macro that when executed, will automatically run through a document and convert all these characters to their English equivalent. ie, A, A, A, E, E.

I've searched the forum and cannot find a thread that specifically applies to this problem.

can someone give me a head start?

any help would be much appreciated.

Automatically Run Macro When Formula Result Changes Cell Values
How do I automatically run a macro every time a formula result changes a cell value?

PS: Could answers include an example in code?

Ignoring Or Automatically Answering Dialob Boxes During Macro Run
The VB macro I'm developing generates a few application specific dialob boxes during execution. I am looking for some way to ignore them or to answer them.

This is the same 'Mail Merge' macro I've asked questions about in earlier posts. I want to thank those who have answered me so far. Your replies have been hugely helpful and enabled me to near the end of this program's development.

For this thread, the macro starts off in Excel. It then controls Word for a bit to do some Mail Merging. During this process, Office opens a brand new Excel window and opens the Mail Merge source file during the DDE processing. It's at this time the first box appears. Since the program starts off in Excel and it's opening a new Excel window, I get a small dialog box that says the file 'personal.xls' is in use and gives some options 'Read only' 'Notify' or cancel.

I'd like to be able to automatically answer 'Read only' for this box so the program continues execution without the user pressing the 'R' key or clicking on 'Read only'.

The second issue arises during the Mail Merge of some larger files. The Mail Merge takes so long that the original Excel window (where the macro execution started) gives me an error message that 'Microsoft Excel is waiting for a response from another application.'

If I go to the window and click 'Ok' execution resumes but it has to restart the whole Mail Merge process which takes an extra few minutes and has caused a few other text issues.

Would this be an appropriate place to use the Wait method?

I volunteered to use MSO 97 VB to automate some processes at work. That's why I'm asking all these questions. The program works on a basic level now but I'd like it to be much more refined so the user has to do much less. I do appreciate your thoughts on these issues. Thanks for reading!

JT

Selecting A Range While A Macro Is Running
I have a vba macro that transfers data between two spreadsheets.

The macro works; however, it would be better if the user could select the range to transfer while the macro was running.

Currently, before the macro is run, 1) the source sheet must be selected 2) the source range on the source sheet must be selected 3) the destination sheet must be selected.


Code:
FunctionTransferData(lRowDestination as Long, nCol as Integer, strSource as String) as Boolean
'lRowDest is the destination Row in the Destination Worksheet
'nCol is the column being transferred
'strSource is the path to the source workbook
Dim appXL As Excel.Application
Dim lRow as Long
Dim lRowDest as Long
Dim lRowFirst as Long
Dim lRowLast as Long
Dim objSourceWB As Excel.WorkBook
Dim objSourceWS As Excel.Worksheet
Dim objDestinationWS As Excel.Worksheet
Dim rngSource as Excel.Range
Set appXL = Excel.Application
'Debug.Print objWB.Name
Set objDestinationWS = appXL.ActiveSheet
Set objSourceWB = appXL.Workbooks(strSource)
Set objSourceWS = objSourceWB.Worksheets("Data")
objSourceWS.Activate
Set rngSource = appXL.Selection '<--------requires the selection to be made before the macro is run
lRowFirst = rngSource.Row
lRowLast = lRowFirst + rngSource.Rows.Count - 1
lRow Dest = lRowDestination
For lRow = lRowFirst to lRowLast
objDestinationWS.Cells(lRowDest, nCol) = objSourceWS.Cells(lRowDest, nCol)
lRowDest = lRowDest + 1
Next lRow
objDestinationWS.Activate
Set rngSource = Nothing
Set objSourceWS = Nothing
Set objSourceWB = Nothing
Set objDestinationWS = Nothing
Set AppXL = Nothing

End Function
Is there a way to select a range while a macro is running?

It would seem execution must be paused, the selection made and the execution continued.

Any help would be appreciated.

Automatically Select CSV File From Directory
I am working an a project that requires me to write some VB code in excel to select any CSV file that is exported into a specific driectory and then take that CSV file and import it into an Excel workboox template.

I have come accross a problem and was hoping someone from this wonderful board would help me work through it.

I was able to get this macro to select the file using the following snippet.

'get the file to open
Dim fNameAndPath As Variant
Application.ScreenUpdating = False
fNameAndPath = "c:excel_projincommingcsv01.csv"
If fNameAndPath = False Then Exit Sub

This chooses the fle called csv01.csv from the incomming folder. I need it to pick any files with a .csv extension.

Can anyone suggest how I could do this?

Thank you.

Automatically Select A Radio Button
Hello,

I am trying to get Visual Basic to select a radio button based on a varible. This is for a form to determine a persons gender, M or F. and the variable has been preset with the value of M. What I want the program to do is, if the variable is set to M, select the radio button next to the word Male! No matter what I have tried so far, i get the error "Argument Not Optional"

Here is the code for two different attempts Ive tried and both have errored out with the same error.
-----------------------
Dim var_sex

var_sex = "M"

If var_sex = "M" Then
rbSexM.Item.Value = True
Else
rbSexF.Item.Value = True
End If
----------------------

-----------------------
Dim var_sex

var_sex = "M"

If var_sex = "M" Then
rbSexM.Item.Enabled = True
Else
rbSexF.Item.Enabled = True
End If
----------------------

Thanks for your assistance!

Regards,
~zero

Firing A Macro On Selecting Specific Sheet
The subject says it all really.

How can I get Excel to fire a macro when one of the sheets in my workbook is selected?

Thanks

Francis Neil Beans

Automatically Select An Item In A Listbox Control
Hello all, I'm a C++ Programmer and Web Programmer in PHP. I need to create an application for a project I'm doing. I would like to write it in VB as I am trying to get familiar with that language. After the C syntax VB looks foriegn now. So if my questions sounds really simple forgive.

I'm trying to automatically select an item in a listbox control. Triggered by an event such as a button click or even an onload, I would like to have the listbox index selected from the results of my subroutines.

I'm looking at this, if I want the 22 listindex selected I did this.

Me.lstModelTypeList.SelectedItem = Me.lstModelTypeList.Listindex(22)
Me.lstModelTypeList.SelectedItem.EnsureVisible

can you guys give me a bit of help?

Thanks

Converting An Excel 2002 Macro To An Excel 95 Macro
Hi there, Iíve finally got my macro program to work (thanks everyone). Now I have to load the macro on a much older machine. I wrote the Macro on Excel 2002 and now Iím trying to run it on Excel for Windows 95 Version 7.0. Iíve loaded it up, but there are all sorts of errors. Does anyone know of a simple way to convert the macro code so it will run smoothly on Excel 7.0?

Selecting/De-select All Items In A ListView
On my form that has the ListView control, I have two command buttons, cmdAll and cmdNone. When you click on cmdAll it should check all the items in the Listview and when you click on cmdNone, it should de-select all checked items. how can I ensure that? My present code for checking items is:


VB Code:
Dim i As Long For i = 1 To ListView1.ListItems.Count    If ListView1.ListItems.Item(i).Checked = True Then    frmMovie.lstAudio.AddItem ListView1.ListItems.Item(i).Text    End IfNext


Thanks

Copyright © 2005-08 www.BigResource.com, All rights reserved