Free Microsoft Excel 2013 Quick Reference

What is the ActiveSheet.Name?

This procedure is meant to detect the selected sheet and show one of two possible forms depending on which sheet is selected:

Sub Load_Form()
Select Case ActiveSheet.Name
    Case "One": FormOne.Show
    Case "Two"
        x = CStr(Selection.Address)
        Sheets("One").Range(xcell).Select
        FormTwo.Show
    Case Else: MsgBox "You are not in the correct workbook."
End Select
End Sub
Regardless of which sheet is selected, the message box pops up. If I run MsgBox ActiveSheet.Name it pops up the expected sheet name but if I run the Load_Form code right after without changing anything it tells me I am in the wrong workbook. One second Excel says the active sheet is "One" and the next it is neither "One" nor "Two". The module is located in the Personal workbook if that matters. Please let me know if you know why it is behaving that way.

Thank you!


Post your answer or comment

comments powered by Disqus
I am trying to set up a newsreader for this newsgroup. What is the server
name I am setting up too?

Thanks

G'day there One & All,

I just have a quick question to try to clarify how something
works. My WorkBook contains 8 WorkSheets. Oddly enough, the Project
Browser shows a tree of 8 WorkSheets named: Sheet1(Main); Sheet2
(Roster); Sheet3(Staff); etc.

The Properties Window indicates that each sheet has a "(Name)",
eg. "Sheet6", and a "Name", eg. "Equity". I know that they don't refer
to the same thing, but what is the difference between the 2?

I have previously used the "(Name)" to refer to each sheet by
changing its value. Eg. Sheet1(Main) became "mSht". This allowed me to
use such constructs as "mSht.Range("Groups").clearcontents". However in
some circumstances, which I never understood & so can't really explain,
this didn't work & I had to also "Dim mSht as WorkSheet" and then "Set
mSht = WorkSheets("Main")". Why is this?

Can someone either explain, or point me to a web based description
of what & why? My current code had a bit of difficulty referring to one
of the sheets so I changed them all back to the "Sheet1(Main)" format
and Dimmed and Set all over the place to compensate. I can't see what
the problem was and I found it easier to refer to sheets as "mSht".

Is it that the (Name) represents the WorkSheet object for the
particular sheet, while Name is just a property? That's the only
scenario I could think of, but I still don't quite grasp the possible
ramifications.

Thanks for listening,
--
Ken McLennan
Qld, Australia

What is the code to add a new sheet with new name?

Hi all,

This is part of my loop code:

ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename & ".xls"
On Error GoTo ErrHandler:
pg.Sheets("Phone_Guidelines").Copy Before:=Workbooks("Store_" & ws.Name & endoffilename & ".xls").Sheets(1)
x = x + 1
mylog(x) = StoreDir & "Store_" & ws.Name & endoffilename & ".xls"
ActiveWorkbook.Close True

The SaveAs StoreDir location is a network path pathblabla all my folders are located on the same server, and I wanted to write an error handling block to deal with the error number instead of displaying a number to the user which would confuse.

What is the error number for when the SaveAs operation fails due to the network drive being down ?

Also, is this the best way to write the error handling code ?

I guessed the error number, where can I look them up ?

ErrHandler:
If Err.Number = 1004 Then ' network drive is down, so msg user and Exit routine
MsgBox ("It appears that the Store Network Drive is down, existing script. Please wait for the network drive availability before executing this script.")
Exit Sub
End If

Hi

I've used DataPig to pass out the data on multiple tabs and their names all end with "-". I want to make it away. Could anyone tell me what is the fastest way to remove it from the multiple worksheets's name? Thanks.

I am running some code to save a worksheet to its own excel file. What is the syntax to allow the path to be generic, to save the file to the working directory, where the excel workbook file is located. In this example the workbook is in the directory "Excel Dashboard". What if the directory I want to save to is one folder below the working directory, "Excel DashboardInvoices"?

Sub SaveInvWithNewName()
    Dim NewFN As Variant
    ActiveSheet.Copy
    NewFN = "D:DocumentsDropboxExcel DashboardsInv" & Range("F3").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub
I am writing the program on Win 7, but it will also be used on a Mac, will the syntax for the path need to be different?

Hi,

What is the fastest way to find and replace characters in VBA.

I have spreadsheets that need punctuation and certain words removing, and at present i have it run a find and replace on all of these chars one by one.

What is the fastest way please? (I also need to be able to have them removed in a certain order)

Cheers

what is the difference between them.
If i know how many rows there are in a column I guess i can just use MAX, right?

What is the best practice to overcome 1024 formula bar limitation?

Thanks in advance,
Mark

What is the code to exit from Excel program. as when you click X

I do computer training and spreadsheet projects on the side, but some of the things people are asking me to do will involve VBA (I am pretty sure). What is the best way to learn how to write the codes, etc.?

I have 3 data sets in Excel

1. List of Products (about 20,000)

2. Product Categories (about 50)

3. Product Suppliers.

What is the best method in Excel (or other software) to show the links between each Product, Category, and Supplier?

Many thanks,

I saw it from my previous post

http://www.mrexcel.com/board2/viewtopic.php?t=303670

so may i know what is the difference between $A$1 and A$1?

thanks!

And what is the syntax for ArcSinus?

What is the simplest VBA code to exit a sub using On Error?

Thanks mikeburg

Hi All:

Perhaps it is not a suitable question asking here, hope you all don't mind.

Work as a database,
What is the differences between Microsoft Access and Excel?
e.g.
1. What is the optimum operation size of access and Excel as a database?
2. How many record (one row for one record) each of them can store?
etc.

Thx your concern

could anyone tell me what is the use of Data>Consolidate?
I want to know what are it's functions and when does it come useful.
All help will be highly appreciated. thanks in advance

hi folks;
I have a formula in a cell like that:
{=TABLE(date,8.5x)}
what is the function TABLE used for?
any help will be appreciated.
thx

Can someone explain this nested IF's?

=IF($A27>$B$6,0,IF($N$9=4,1/400,IF($N$9=2,1/200,1/100)))*$B$5*J$21*$E27

What is the false statement?

Refer to http://www.decisionmodels.com/memlimitsc.htm

Excel 95, Excel 97 and Excel 2000 are officially limited to 64MB of formula
memory for workbooks (heap space).
Excel 2002 is officially limited to 128MB of formula memory for workbooks
(heap space).
Excel 2003 is officially limited to 1 Gigabyte (GB) of memory. This limit
appears to a limit on the working set memory used by bthe Excel process,
which is the memory reported by Windows Task Manager.

What is the limitation of formula memory for Office XP - Excel XP?
Does anyone have any suggestions?
Thank for any suggestions
Eric

I need a normal scientific chart. You know, the ones with axes on the
right, left, top, and bottom sides. I think I did this once before and
it took an hour, since I had to trick excel into thinking i had about
four different sets of data, most of which were fake. It was insane.
Meanwhile something like Mongo or Axum or ?? or IDL would do this
naturally (make good scientific charts instead of bubble gum
candy/girlie/kiddie charts).

What is the best option for me? Do I go with a simple template a
consultant has worked out (speak up please), an excel add-in (which
one?), or do I really need to ditch excel unless all I was doing was
adding 2+2? I'd prefer not to have to toss excel and am hoping there
is a charting add-in I can buy.

Jason Taylor

--
jasonataylor
------------------------------------------------------------------------
jasonataylor's Profile: http://www.excelforum.com/member.php...o&userid=37102
View this thread: http://www.excelforum.com/showthread...hreadid=568421

My question is two-fold:
If I have a formula driven cell (i.e. the cell syntax in A1 looks as follows
"=F32") I can double-click A1 to have the selector move to F32. How do I turn
this dunctionon an off?
What is the keyboard shortcut for the double-click? (NB - not the shortcut
for editing, I know its F2).

Any help appreciated.

what is the keyboard shortcut for generating a sequence.
for eg: 1 2 and then dragging to our desired limit etc.
or typing jan feb and dragging

What is the formula for subtracting the sum total of two cells in nonadjacent
(nonconnecting) cells?


No luck finding an answer? You could always try Google.