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


Advertisements:










Tell VBA To Deselect


I have a macro that is copies and pastes data from one area to another. When it is finished it currently leaves the cell range on sheet it copied from selected. I would like to have it deselect that range of cells and return the sheet to cell A1. Basicaly what i want is for it to perform the same operation as hitting Esc.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Deselect Rows
Is there a way to desselect one specific row from a selection that include 2 or 3 rows?

For example: I have selected rows nº 2,3 and 8, and then I want to deselect row nº 3. Is this possible?

View Replies!   View Related
Deselect Items In Listbox
I can't seem to get my listbox to deselect an item. When the user clicks on a day of the week in the listbox, they are given a yes/no option to print the sheets for the day selected. If they select no and change their mind, they cannot click on the same day of the week without first cancelling the listbox and re-opening it.

Private Sub UserForm_Initialize()
Dim myarray As Variant
myarray = Split("Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday", "|")
ListBox1.List = myarray
End Sub

Private Sub ListBox1_Click()
Call PrintRoutine(ListBox1.Value)
End Sub....................


View Replies!   View Related
Code To Deselect Checkbox
Can code be written to deselect a checkbox.

Something like:
Deselect.checkbox1 = TRUE
Or maybe execute code using code

If Range("C1").value = TRUE then
MakeWide
Else
EndIf
I have the MakeWide Macro attached to checkbox1

Will this actually physically change the checkbox? to select/deselect?

View Replies!   View Related
Deselect Sheet After Copy
I've got this simple code which copies an entire sheet to another sheet. What can I do so the sheet being copied will not stay selected after the copy has been done.

View Replies!   View Related
Deselect ListView Item
I have a userform that has a listview in it. In the listview i have bunch of icons with names. I have it where if you double click on the icon, you will see a msg. box which you need to press ok and the box will go away. After you press ok the icon is still selected. I would like to be able to deselect the icon when you click on the listview box (in the white space away from the icon). I tried everything and no matter what i do, I cant deselect the icon. It is either highlighted or has a outline around it (little dots around the icon). This means that i can double click anywhere in the listview box and the and the msg. box will pop up.

All I want is when i double click the item, the msg. box should come up and when I click away from the icon (click in the white space) the icon should be completely deselected.

View Replies!   View Related
Deselect ListBox Item In Code
I think this should be simple to answer. I have a userform that has multiple listboxes on it. When the user makes a selection in Listbox1 it highlights the item selected then when the user makes a selection in Listbox2 it highlights that next item and the selection in listbox1 remains highlighted. What I want is to deselect the selection in Listbox1 when Listbox2 is selected. I have tried things like below:

Sub Listbox2_Click ()
Me.Listbox1.Deselect
End Sub

View Replies!   View Related
Deselect Selected Items In MultiSelect ListBox
When i tranfer my listbox content to sheet the selected item remain selected in listbox

Dim i As Long, j As Long
For i = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(i) Then
j = j + 1
Worksheets("Workings"). Range("AA" & i + 2).Resize(1, 1).Copy Worksheets("Print").Range("B" & Rows.Count).End(xlUp).Offset(1)
ListBox1.Selected(i) = False
End If
Next i

View Replies!   View Related
How To Create VBA To Delete Previous VBA Modules
I would like to create some VBA that looks for sub's or modules and delete them.

View Replies!   View Related
Cell Address In VBA :: Reference A Range In VBA
I have a variable which contains the column number. How do I then reference a range in VBA using that column number, as opposed to the letter? I know how to convert the number into the letter, but I also know there has to be something more simpler. For instance, a form of the ADDRESS function, but in VBA?

View Replies!   View Related
VBA Code Quicker Than Just Writing Out The Calculation In VBA
Does Excel handle formulas written into the VBA code quicker than just writing out the calculation in VBA?

I have a section where I use the following formulas, sumif, countif and a combo if iserror sumproduct in the VBA code...runs rather slow at this point and was looking at a way to speed things up.


View Replies!   View Related
VBA: Seperate Vba Code From File
Is there a way to put vba code to a standard alone exe file? This way, I don't have to keep update the code on different excel files.


View Replies!   View Related
Disable The Vba In A File Being Opened Using Vba
I have excel vba code to open .xls files on my server. Files to open are chosen by the user via a UserForm within my app, it shows them all their files in their directory on my server. How can I open their files (.xls) that they select but DISABLE the VBA from being able to run/execute, if they have any attched to the file?

I now have all code in place to open the file no problem, I just need to know how to disable the VBA part from being "turned on" when the file opens.

View Replies!   View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)

View Replies!   View Related
Distributing VBA Code Changes Via VBA
I have a number of identical workbooks used for data capture within the organisation, and need to continue development as time goes on.

Whenever I make a change to the core Excel functions of the workbooks I simply use a VBA procedure to conduct this change within each of the production workbooks, however I am stumped as to how I can achieve this for changes to the VBA code.

I have made some changes to the code held within one of the modules, and have also modified the form slightly. What I want to be able to do is run a VBA procedure that either updates the production workbooks from a central source, or by importing previously exported code / forms.

View Replies!   View Related
Insert Vba Code With Vba
I have a code that needs to be placed on any new worksheets created. All new worksheets are made using VBA code already so they just need to be told to add the code into each new sheet. How do I do this?

View Replies!   View Related
Using VBA In Patch Model To Change VBA In User Model
I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.

The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.

The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.


View Replies!   View Related
Protect VBA Project Or Lock Project For Viewing By VBA
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View Replies!   View Related
For / Next In Vba
I'm trying to get a for next VB in Excel to work. The script is fairly basic and just copies data from (constantly changing) cells to an array of other cells.

The code is:

View Replies!   View Related
VBA Help Not Available
On my home computer I have Excel 2003 and my Operating System is Vista Home Premium Edition 32-bit. I encounter a problem whenever I try to look up Functions in the VBA HelpIndex. For example, I type "IsEmpty" in the Search help box and I get 7 results...the first one being "IsEmpty Function (VB for Applications)" is the one I'm interested in. Yet when I click on it nothing happens -- no error message, no message box, no nothing. I don't have this problem at work (the operating system there is XP). I've tried uninstalling and reinstalling Excel, but that hasn't solved the problem.

View Replies!   View Related
Subtotal In VBA ...
Trying to do a Subtotal in VBA...

View Replies!   View Related
More Than 7 Nested If -Then - Else Through VBA
My objective is to replace a text string with a numeric variable. The string has text values such as US, Canada, Germany, France etc, There are about 15 such words in total. Each row will have a different one of the 15 text strings.

Each text string has a corresponding number, which I want to use in various formulas throughout the spreadsheet. The numbers are located on a separate worksheet (“Match Rate”), within the same workbook. I obtained the numbers from a formatted report, which I simply pasted into the second worksheet. Due to the complexity of the report, I can’t reformat the numbers to allow use of the VLOOKUP function.

I created the following function, but unfortunately it doesn’t work. Can you help, please?

I’d like to stay with a VBA solution, since a simple if/then sequence such as this is an excellent introduction to the powerful world of VBA... (it just doesn’t work, yet). I suspect the problem is in the calls to the Match Rate Worksheet.

View Replies!   View Related
Login Using VBA
I need to create a login username and password form which pops up when excel worksheet is open

Does anybody know the instructions for this task


View Replies!   View Related
VBA For Next Statement
Im using this For next statement but doesnt work. Im trying to sum up A1:B1 in C1 and so on. Im trying to figure out the For Next statament so would appreciate your input.

Sub Mac1()

Dim r1 As Range
Set r1 = Range("a" & i, "b" & i)

Dim i As Integer
i = 1

For i = 1 To Range("a1", Range("a1").End(xlDown))
Application.WorksheetFunction.Sum (r1)
i = i + 1

Next i

End Sub

View Replies!   View Related
VBA To Copy A Value Elsewhere
I just don't know how to write this!!

I need to add code to do the following:
If there is a number (all should be text) in column C then take that number up one row and over to column T and put the number there.

There really should not be any data past 200 rows so you could actually stop there instead of column C to the bottom of the page.



View Replies!   View Related
VBA For Any Old Workbook
Is there a way to "generalise" VBA code, so that it executes the same action in every worksheet of any other open workbook, regardless of how it's named?

I'm writing some simple VBA code to copy the contents of columns and paste them in a different workbook. The columns are the same in every workbook and every sheet within that workbook but the workbook/sheet names change.


View Replies!   View Related
Multiply Using VBA ...
How can I multiply a range with a value and put the value in another range, using VBA ?

I have in some cases 2000 -3000 rows and I need to automate the process.

Please open attached file!

View Replies!   View Related
Looping Without Using VBA
Is it possible to write a cell formula (not a macro) to loop through a range of cells and test for a value? Something tells me that I need to use the "Offset" function.


View Replies!   View Related
Save As VBA
I would like some code to save the "current workbook" as the current name with the number 2 on the end of the name. And save it to the same path as the current file.

View Replies!   View Related
Name Ranges In VBA
I am trying to locate a named range cell in my VBA macro, but find that VBA will only allow me to specify the actual cell eg. Range("E2").


The piece of code I am using below is

Sheets("sheetname").Select
Range("E2").End(xlDown).Offset(1, 0).Select

I would like to use
Range("namerange").End(xlDown).Offset(1, 0).Select

Do i need to Dim or specify the name range in any particular way.


View Replies!   View Related
Error In VBA Because Of Name
I get an error in my code here:

fname = Dir(strPath & Range("B11") & "*.xls")

I get the error because the strPath is wrong because there is not a connection. How can I get a message box that makes sense to come up and say "You need to make sure you are connected to the network, then try again." When this particular error occurs.

Sub SendToChuck()
Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
Dim strPath As String, fname As String, i As Integer
' If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Sheets("ESTIMATING").Select
ActiveWorkbook.Save
' strPath = "\SidonnacEstimating"
strPath = "\553.56.288.661New Estimting"............


View Replies!   View Related
VBA If Value In J And I Is Empty Then
My code is as below... its failing with message "Else without If" but there is an If .. i've tried adding Ifs and putting in With and many variations but to no avail.

i'm . Column J is a number and Column I could be number or text but is formatted as text.

Sub testvalues()
Dim lastrow As Long
Dim r As Range
Dim Msg As String
Dim b

View Replies!   View Related
Sort VBA
I need some code to attach to a button that will sort data in H3:CA770 in descending order, the catch is I need this to sort on by the column to the most right with data in it.

I use rows 1 & 2 as a header so there will always be data in that, so I need the button to sort my worksheet based on the last column with data in it. The sheet will be updated monthly so I dont want to creat 12 different sort buttons.

View Replies!   View Related
VBA If Statement
is it possible to check a value cell vs value in a range? something like this.

If Range("F65536").End(xlUp) Sheets(2).Range(Range("G2"), Range("G65536").End(xlUp)) Then
MsgBox (Check total)
End If

I am working with two worksheet workbook. sheet one has a cell which contains the total amount value from sheet two and sheet two has the details for the total in one column.

View Replies!   View Related
VBA Coding
I am trying to code a macro to run down through a column and hide the row if it finds a cetain marker (i.e. the letter "n") in the column, and then move onto the next one.

View Replies!   View Related
VBA Ranking
I have a sheet name "GP", and the range "A1:A5".

I need your kind help with a code that if the cells in the range become 10, then the cell across in column B will rank the order of the cell that scored 10.

ie: if A2=10 then B2=1st, A4=10 then B4 = 2nd, A5=10 then B5=3rd, A1=10 then B1=4th, A3=10 then B3=5th.

The ranking is placed according to the which cell results to 10 first.

View Replies!   View Related
Function In VBA
why the below code does not work?

Sub AnsiSub()
ActiveSheet.Cells.Select
Selection.Formula = Value(Trim(Substitute(A3, char(160), char(32))))
MsgBox ("Complete")
End Sub

View Replies!   View Related
Vba Concatenation
why this code continues to give an error?

Sub TakeOut()
'
'
Windows("EXTRACT2.xls").Activate
Sheets("Sheet1").Select
For i = 1 To 100
Range("A" & i).Formula = "=CONCATENATE(""Trip "", ""=RIGHT('[EXTRACT.xls]Sheet1'!A & i,3)"", "" = "", '[EXTRACT.xls]Sheet1'!E & i)"
Range("A" & i).Value = Range("A" & i).Value
Next i

End Sub

View Replies!   View Related
VBA - If Or Statement
I want to create code that will check if a number of cells is empty and if they are to call a sub. If any of the cells are not empty then call another sub.


View Replies!   View Related
Mail With VBA
I have written a procedure to send out mails to a distribution list with the required attachment attached to the mails.

However, before the procedure (for sending mails) runs, I want to check if I am using the correct Outlook Mailbox to send the mails (my job requires me to use multiple mailboxes, and I cannot send out mail from a wrong mailbox; however, accidentally I may use wrong mailbox to send the mails).

So, I need the procedure to identify the correct mailbox.


View Replies!   View Related
Superscript Vba
I have a scheduling spreadsheet that I designed for excel. It uses numbers as text and vlookup values instead of calculating actual time (its easier and saves space). I want it to look aesthetically pleasing:

630-230 (that is six-thirty until two-thirty)
7-315 (that is 7 o'clock until three-fifteen)

should look like this:

630- 230
7 - 315

How should the code be written so that if I write a string of text "xxx-xxx or x-xxx" the right parts get superscripted automatically.

notice that the first example has 3 digits before the hyphen and the second example has 1 digit before the hyphen. I need the vba code to work no matter which example is used.

View Replies!   View Related
Validations In VBA
on VBA in regards validation rules in Excel?

View Replies!   View Related
Autofill Using VBA
I have a spreadsheet with data where the number of rows change daily, in column D.
I have formula in column E which I autofill manually by double clicking on the cross thingy of the first cell.
However I'm trying to do it using VBA, but it always stops at a certain row and not where the column D data stops.

Below is the macro which stops autofilling at row E7762

Selection.AutoFill Destination:=Range("E3:E7762")

Is there a way to amend the VBA to autofill based where Column D data stops?


View Replies!   View Related
VBA - Another ComboBox
I'm populating a combobox with the data in 3 columns.

Question 1: The range has 3 Columns though my combobox is only showing 1 how can I get it to show all 3?

Question 2: I'm sure this would be much better served by a loop, populating the combobox, any examples?

For Each c In Range("DefectID").Rows
Me.Defects.AddItem Cells(c.Row, 3)
Next c

View Replies!   View Related
Sumproduct In VBA Getting #Value
I've got a spreadsheet that had _many_ sumproducts and calls to vba functions and when it recalculated took some time to finish, so I thought I'd group the vba function with the sumproduct to see if it was faster, but I keep getting a #Value! error. The sumproduct layout worked fine on the actual spreadsheet, so I'm sure something is lost in translation. Here is my
Function weight(Aref As Range, count As Integer, Bref As Range) As Variant

Dim i As Integer
Dim top As Integer
Dim vA() As Variant
Dim cA() As Variant
Dim vR As Range
Dim cR As Range

Dim count As Integer

count = Widgets.Rows.count

ReDim vA(count - 1)
ReDim cA(count - 1)


top = Aref.Rows.count

For i = 0 To top - 1.............................

View Replies!   View Related
Charting In VBA
I have an existing chart and I want to update the plot utilizing data that has been calculated and stored in 2 arrays X(N,1) and M(N,1) via my VBA macro. How do I do it?


View Replies!   View Related
VBA Protection
I have a macro that copies the contents of a cell, and pastes it into the the first blank cell of a range. Its important that the entire sheet is protected, but the macro won't allow the paste function because of the protection.

Is there a VBA code to unprotect the sheet, run the copy/paste macro, then protect the sheet again. THe problem is I would prefer the protection to use a password, as I don't want the user to simply unprotect the sheet from the menu bar.

View Replies!   View Related
VBA Password
When using the a file with VBA based on a button is there any way to add a password to that file at the same time?

That way it can be opened by anyone?

The code below works fine but the user can open the file in fact anyone can once they press the command button I want to add a password so when the file is reopened it requires a password?

Is this some thing vba even does?

Private Sub CommandButton1_Click()
Const PTHNAME As String = "C:Feedback"
Dim FileNameWithDate As String, x As Integer

FileNameWithDate = Format(Date, "yyyymmdd")

x = 1

If Dir(PTHNAME & FileNameWithDate & ".xls") = "" Then
ActiveWorkbook.SaveAs Filename:=PTHNAME & FileNameWithDate & ".xls"
Else
Do Until Dir(PTHNAME & FileNameWithDate & x & ".xls") = ""
x = x + 1
Loop
ActiveWorkbook.SaveAs Filename:=PTHNAME & FileNameWithDate & x & ".xls"
End If
SendMailtoBDTT
SendMailtoPerson
End Sub

View Replies!   View Related
Rounding Up Using VBA
What is the code to round up a range of numbers at certain decimal.

View Replies!   View Related
How To Use Correlation In Vba
concerning the use of correlation in vba. What are my options when i want to calculate a correlation within vba. The variables are all still within vba.

Who knows what formula's i could use to keep everything within vba.

If you don't understand my question please ask so i can try to make it more clear.

View Replies!   View Related
If Formulas In VBA
I have a sheet of data which is refreshed eash day, the data has frequencies and values in it. I need the code to say:

if column E:E = Monthly, and column M:M = Annually then divide the value in column N:N by 12

If column E:E = Monthly, and column M:M = Quarterly then divide value in column N:N by 4

We have these four freqencies:

Monthly
Quarterly
Half Yearly
Annually

and the above code will need apply to all scenarios i.e. if E:E = Quarterly and M:M = Monthly then x N:N by 3.

E:E being the origonal frequency and M:M being the new one, we need to know the value of the new gift at the old frequency.

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