Re: Save As In Beforeclose Event
Thanks, I'll try that.
Re: Save As In Beforeclose Event
Thanks, I'll try that.
Re: Save As In Beforeclose Event
The file should ALWAYS be saved as the same name. It will always be save as since they may be saving it as another name for their records. That is OK but I need it to be saved back as the same name also. I use this name in another macro which opens and does some stuff with the data in the file. They are updated monthly. I just thought there might be a way to make the save as prompt automatically default to yes. Doesn't seem like it though.
Re: Save As In Beforeclose Event
Is there any way to make it default to YES?
I need to use the BeforeClose event to save the workbook with the same name when a user closes it. (82 users and they all seem to want to give it their own name.) I have the following code in the BeforeClose event but would like to eliminate the prompt "this file already exists, do you want to replace it" I have tried using the Application.DisplayAlerts = False but this seems to stop the workbook from saving.
I have a public demention called wbName and is set to the workbook name in an outo open macro
Public wbName
Public Sub auto_open()
wbName = ActiveWorkbook.Name
UserForm2.Show
MsgBox "PLEASE do Not insert rows/columns or enter calculations" _
& Chr(10) & " " _
& Chr(10) & "Enter Only Account Name, Date, and Corresponding_ Calls/Details", vbCritical, "Caution"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=wbName
End Sub
I have searched this forum but have been unable to find the answer.
Re: Set Autofilter To All Without Removing The Autofilter
Thank you
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
Re: Test If Cell is Empty or Blank
That gives me what I need.
Thanks
I want to add an IF statement to my macro that will run only if a cell on the worksheet is not blank (empty). I know how to use the if statements but can't figure out how to test a cell for contents.
Re: Insert (paste) Rows Of Data Into Block Of Data On Another Sheet
Still includes the headers and copies over existing data rather than inserts.
Re: Insert (paste) Rows Of Data Into Block Of Data On Another Sheet
Sorry, I am pretty new at this. I have enclosed a copy of the workbook. I have rows filtered on Sheet2. I want to insert the visible cells less the header to Sheet1 cell A7 which is named EndT1.
Re: Insert (paste) Rows Of Data Into Block Of Data On Another Sheet
In the same workbook different sheet. I have a range name to select the insertion point.
I need code to copy rows from a filtered list on one worksheet and insert at a specific point in another worksheet. What I would do if doing it manually is to "select the visible cells, copy, go to the other worksheet, select the point I wanted to insert the data and select Insert Copied Cells". When I try to record this with the macro recorder I don't see the option Insert Copied Cells. Is there a way to do this with VB?
Re: Formatting /Vlookup issue
Found the perfect fix for this problem (numbers formatted as text) from the book "Excel Hacks" by D & R Hawley. Hack #26 copy a blank cell, select the cells with the problem, paste special using values and add. Works like a charm. I should have had this book months ago. Filled with great work arounds!
Re: Preform Action/run Macro On Double Click
Answered my own question. By simply putting the code in the workbook object for Workbook_SheetBeforeDoubleClick it works.
I want a X to be put in the activecell if empty or removed if already there. By searching this forum I found and read the article "Adding a Command Button to the Excel Right Click Pop-up Toolbar/Command Bar" and was able to add the code to do this to the right click sub menu. Is there a way to do the same thing by double clicking on the cell? This probably seems a bit nit pickey but I would just like for it to work using double click for my users.
I wrote the following code to performe the action
Sub Toggle_Macro()
If IsEmpty(ActiveCell) = True Then
ActiveCell.Value = "X"
With Selection
.HorizontalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = 3
End With
Else: Selection.ClearContents
End If
End Sub
Display More
And placed the code below in Private Module of the Workbook Object
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cBut As CommandBarButton
On Error Resume Next
With Application
.CommandBars("Cell").Controls("Toggle").Delete
Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
End With
With cBut
.Caption = "Toggle"
.Style = msoButtonCaption
.OnAction = "Toggle_Macro"
End With
On Error GoTo 0
End Sub
Display More
Re: Select Data In A Column To Last Cell
Works perfect, Thanks
First let me say how much I appreciate all the help, I have learned so much from all of you.
I have a macro that loops through several workbooks and copies data to one common sheet. Works great with one flaw that I have not been able to resolve. I am using the End Property example below, this works great as long as there are more than one cell with data. With one cell having data this takes you to the bottom of the sheet.
Any suggestions on code to select from a specific cell to the last cell in that column with data where when the selected cell is the only one with data that would be the only cell selected.
Re: Open Many Files/Workbooks & Run Macro
Quote from Dave HawleyThe 2nd is yes, but NOT 1st code. Which is obviously the code I made reference to not being the same. I think you know that.
You can question the spirit of this forum if you like. But frankly you are the one seeking FREE help and as such really are not in a postion to question anything. There are many thousands of Excel users freely helped on this forum every day of the year. Either by posting questions and/or searching. I myself freely help about 10 per day on this forum and as many again via email. I think you should check your facts before questioning anything.
I simply have no time to help someone who plays semantics and questions the "spirit" of a free question forum. I will focus on those that appreciate help.
I agree, this is a wonderful forum. I have gotten so much help and learned so much in the process. THANKS TO ALL OF YOU WHO PROVIDE THIS HELP!!!!
Re: Populate Form Listbox Rowsource With Code
That worked, thanks much