Free Microsoft Excel 2013 Quick Reference

VBA: how can allow unhide of columns only with a password?

Hi,

In my worksheet I have over 60 columns of data. When I review this worksheet with other departmental staff and also my customers, there are certain columns I would like to hide them but keep them in tact for my helpful reference.

I want to prevent others from unhiding those columns and prevent from seeing those sensitive information.
Can I hide the columns with a password ?

Any help on this matter is greatly appreciated. Thanks a lot !


I have a workbook that has 10 data worksheet and from these I create another 10 charts (not embedded, just chart sheetS).

How can I print the charts only with a macro? I have found many examples to print charts embedded in worksheets but none that just prints any chart found in the workbook.

Any ideas?

The macro I've been trying is the following (trying to print a worksheet by matching a string to its name - since "chart" is always included in my chart sheets name)
Sub PrntAll()
Dim match as string
match = "chart"
dim sheet as worksheet

for each sheet in thisworkbook.worksheets

if Instr(1,UCase(sheet.name),ucase(match),1,) > 0 then
sheet.printout copies:=1
end if
next

end sub
I tried playing around with capitals and small letters but it won't work.

How can I format an Excel cell with a "save" or other command?

Hi, how can I configure Excel to open with a particular file whenever I open Excel itself. The help function says instal the file in same directory as Excel. I tried without result. Any Ideas? Thank you

RD

How can I unhide hidden columns and then hide them again by macro? suppose
that I have a workbook made of 10 columns. colum 3,4 and 5 are hidden by
default. I want to unhide these hidden columns when I type word "AB" in any
cell in colum Number 1. and then by pressing enter I want to hide these
columns again. is this possible using macro?

Many thanks
Hoshyar

Is there a function that can add up the values of cells but only if a
corresponding cell has information on it. For example i have two
columns of lists. The left column has every cell filled up with a
number, but the right column has a few blanks. How can I add up the
cells of the left column that only have a number in the right column?
THanks

--
zuri125
------------------------------------------------------------------------
zuri125's Profile: http://www.excelforum.com/member.php...o&userid=31131
View this thread: http://www.excelforum.com/showthread...hreadid=549312

In a formula, how can I reference the first value in a column of numbers when the row number of that first number is not constant [ie] the row number changes regularly from day to day as I insert new rows at the top of the column each day?
[and the row number is always greater than 1]

Is there a function that can add up the values of cells but only if a corresponding cell has information on it. For example i have two columns of lists. The left column has every cell filled up with a number, but the right column has a few blanks. How can I add up the cells of the left column that only have a number in the right column?
THanks

Dear all,

I am creating a new project with a very tight deadline and I am having trouble with some crucial details. I need to secure some of the sheets in my workbook with a password so that they can be accessed only by certain people with a specific password. The data also must stay hidden unless the correct password is given. I searched the internet and found an VBA code that is fitting my needs. There is a problem however. As I am an beginner with VBA, I am not able to modify the code so that it is fully working. Everything works fine, but the main problem is that the data are shown even if an uncorrect password is inserted. I don't have a clue how to fix this. Not being able to secure some of the lists in the way described above will mean a big problem for me. Could somebody please help me? Every suggestion is much appreciated!

My modified code is:
I need to secure "Sheet2" which is named e. g. "Effectifs" with the password "Heslo123" and the user has 3 attemps

Dim correct_pass_given As Integer
Dim hide_sheet As Worksheet

Private Sub Workbook_Open()
correct_pass_given = Heslo123
'Set hide_sheet = Sheet2 '
Sheet1.Select
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim strPass As String
Dim lCount, number_of_tries_allowed As Long
Set hide_sheet = Sheet2 '
number_of_tries_allowed = 3 '
'MsgBox correct_pass_given

If ActiveSheet.Name <> "Effectifs" Or correct_pass_given = 1 Then
Else
hide_sheet.Columns.Hidden = True
'Allow 3 attempts at password
For lCount = 1 To number_of_tries_allowed
strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")
If strPass = vbNullString Then 'Cancelled
MsgBox "Password incorrect", vbCritical, "Message"
Else: correct_pass_given = 1 'Correct Password
Exit For
End If
Next lCount
If lCount = number_of_tries_allowed + 1 Then '
Exit Sub
Else 'Allow viewing
hide_sheet.Columns.Hidden = False
End If
End If
End Sub

How can I perform statistical operations on groups of (column) values with a common index? The group index is always increasing, the group size varies.

In the example spreadsheet below I would like to calculate the Mean and StdDev of the Values that have INDEX 17, and display results in the first (or last, or all) rows of the group. Then do the same for the Values with INDEX 19, 24,....

INDEX Value Mean StDev
17 3
17 4
17 5
17 5
17 2
17 5
17 7
19 3
19 1
19 4
24 2
24 5
24 7
24 3
24 1
24 4
24 3

Thanks and Regards,
Jim

id like to know how can i multiply two columns. in excel, if i have numbers
on column c and want to multiply it with column f, what should i do?

how can i select all the cells with same color on a sheet if there are
multipale colors by vba code

hi all, i am new to VBA and am looking for some assistance.
i found a great tutorial from Ron DeBruin, for emailing as html in outlook. i was hoping someone could help me adapt the code i have to fit my needs.

What i am looking to do is this;
i have a folder that has 70+ workbooks, all of them named for a different region of the U.S.

i would like to change the code i am using to let me choose the folder with all my workbooks (it changes from month to month), take the first worksheet in each workbook and create individual emails in outlook for each workbook.

Ideally I'd like it to be able to associate the name of the workbook with a distribution group in outlook, but i'll settle with just having the email open and i'd type that in myself.

I'm using both office 2007 and 2010.

Thank you all in advance!

here's the code that i currently have working as of today:
Function RangetoHTML(rng As Range)
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook
2007, and Outlook 2010.
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
 
          
    ' Copy the range and create a workbook to receive the data.
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    ' Publish the sheet to an .htm file.
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    ' Read all data from the .htm file into the RangetoHTML subroutine.
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    ' Close TempWB.
    TempWB.Close savechanges:=False
     ' Build the string that you want to add.
    
    ' Delete the htm file.
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    Dim StrBody As String


End Function
Sub Jims_Mail_Sheet_Outlook_Body()
' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook
2007, and Outlook 2010.
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 Dim StrBody As String
' Build the string that you want to add.
StrBody = "Here is this months Idle 180 days product report for your region" & "<br>" & _
          "This is line 2" & "<br>" & _
          "This is line 3" & "<br><br><br>"
    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    ' You can also use a sheet name here.
    'Set rng = Sheets("YourSheet").UsedRange

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Idle 180 Days Report - " & Worksheets("sheet1").Range("k21")
        .HTMLBody = StrBody & RangetoHTML(rng)
            ' In place of the following statement, you can use ".Display" to
            ' display the e-mail message.
        .Display
    End With
    On Error GoTo 0
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


I wanted to know how can you start up an Excel Spreadsheet with a Formatted
Template instead of the default worksheet which has no formatting assign to
all columns.

Currently, I have created a formatted template and taking the FTP
spreadsheet an successfully cut and pasted the FTP spreadsheet into the
formatted template. I wanted to automate this process to bring up the FTP
Spreadsheet into the formatted template. Is there away I can add commands to
the FTP Spreadsheet to trigger the formatted template to startup?

Please Advise.

David

how can I blank out columns that are not being used

Using VBA, how can I determine which columns currently have a filter set, and for those that are set, what the filter condition is?

TIA,

Far Farley

I have a large worksheet contains rwos with name and address columns. it is
sorted in ascending order by the last name column and address. I want to
print a list of the names and addresses with page breaks at specific letters.
How can I locate the first row with a last name beginning with a specific
letter so that I can insert a page break? I know I can do this manually by
scrolling throught the list, finding the right row, selecting it and
inserting a page break. However, I would like to create a macro to insert
all the page breaks automatically.

Much has been discussed here about how to build this chart, but I
haven't found anything explaining the possibility of combining it
with
a second y axis. I want to graph the advertising expenditure and
gross
rating points (what would be the return of investment on advertising)
on television for three diaper brands by day part. Day parts would be
represented by the different colors of which columns will consist.
There are 5 of them: day, early, prime, late and over. For each
brand,
I want that two data columns show. The one to the right will represent
the
expenditure in $ and the one to the left the GRPs.

The thing is, there is an abysmal difference between the scale of $
and GRPs. So I want a secondary axis for each column on the right
side
of each brand. This means that columns # 2, 4, and 6 will be 'ruled'
by the second y axis and the other columns of odd numbers will be
ruled by the primary y axis.

I hope you can get the idea of what I'm trying to do here. Thanks in
advance for your help.

I know how to password protect a worksheet, however, this still allows
someone to delete this worksheet. Is there a way to only allow it to be
deleted with a password?

Wednesday afternoon

Is it possible to measure/display the column width with a cell function?

I need to adjust some columns for a report.

But I cannot find a way to display the current width in units that I can then place in a VBA macro.

Would be very good to have the macro read the values entered in a set of columns and change the width accordingly.

(This is very easy in Quattro pro.)

J

Is there an easy way to create a report of columns based on a list of data? I know you can do it with a pivot table, but that produces a vertical list. I want each action to be a column heading with the contractID's under the appropriate action. Many thanks.

List of data:
Action ContractID
Amend and Extend CntrctID1
Amend wout Extend CntrctID2
Supersede CntrctID3
Supersede CntrctID4
Amend and Extend CntrctID5
Leave Intact CntrctID6
Supersede CntrctID7

Desired Report:
Supersede Amend and Extend Amend wout Extend Leave Intact
CntrctID3 CntrctID1 CntrctID2 CntrctID6
CntrctID4 CntrctID5
CntrctID7

How can I access and display, from within a worksheet cell, the modified date
in FIle->Properties? In Word you can insert the "Field" in the document. How
can this be done in Excel?

Thanks,
Denny

how do I copy multiple column widths from a worksheet in one workbook to one
in another workbook?

Hi

I have a workbook in which Sheet1 contains a list of unique enquiry
numbers in column E, and a name in column C.

Sheet 2 also contains the same data - Name in column A; enquiry number
in column B and a third column C with a numeric value in it (time spent
on enquiry).

I need to create a formula that will look at the value from E in Sheet
1, match it to a value in B in Sheet 2, then compare the name in column
A Sheet2 to column C in Sheet1. If I have a complete match then I want
to input value from C in Sheet 2 into a new column in sheet 1.

Can anyone help, or perhaps point out a more simpler way of getting
this data across?

Many thanks.

How can I show Feet and inches in a Excel cell?
I'd like to calculate Feet and inches in a Excel sheet