Proper Protection

May 27, 2008

Is there any way I can protect my sheets properly..? I know you can use Tools > Protection.. but I've found (and used, for good, not evil!) macros on the web that will crack these in seconds. Is there any way I can disable the 'Tools' menu so that other users can't load these password crackers?

View 9 Replies


ADVERTISEMENT

Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility

Aug 18, 2004

I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View 9 Replies View Related

Proper Filtering

Jun 4, 2009

Is there a way to filter my data with the combobox and have the listbox reference the filtered data directly?

I am populating a listbox from a rowsource, housed in a sheet in my workbook, the sheet in my workbook is populated from another sheet that is filtered by a combobox selection and pasted to the sheet with the rowsource range.

My listbox is a multiselect with 5 colums and columnheads, named ListBox42

The sheet with the data to be filtered is named Building Material

Building Material is filtered in column “M” with a color

With ComboBox24 selected name: Names “M1” which activates the color down the column for filtering.

The ComboBox24
Private Sub ComboBox24_Change()

Worksheets("BuildingMaterial").Range("M1").Value = ComboBox24.Value
Sheets("BuildingMaterial").Select
ActiveSheet.Range("$M$1:$M$4247").AutoFilter Field:=1, Criteria1:=RGB(0, _
176, 240), Operator:=xlFilterCellColor
Sheets("matfilter").Cells.ClearContents
Sheets("BuildingMaterial").Select
Range("B1:F4249").Copy
Sheets("matfilter").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("BuildingMaterial").Select
ActiveSheet.Range("$M$1:$M$4247").AutoFilter Field:=1
Application.CutCopyMode = False
Sheets("matfilter").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Tom").Select

End Sub

My RowSource is a defined name “=matfilter!$A$2:$E$4230 “

View 9 Replies View Related

Proper Conversion To Numbers?

Jun 24, 2014

So I record a macro but it see only following bit

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

I want this selection to be converted to numbers but in proper way (There is a green thing in corner in every cell and on top of column a yellow field with exclamation mark. When whole column is selected and you apply appropirate convert function from this exclamation mark menu it will convert it properly.

While if you just click on excel tab and choose numbers it just does not change it to numbers

View 2 Replies View Related

VBA Convert To Proper Case

Jun 30, 2013

I've had good success with the following line of code

Code:
Sheets("Test").Range("A2") = UCase(Sheets("Test").Range("A1"))

But how do I do the same, converting the to Proper Case?

View 2 Replies View Related

Proper Case In A Macro

Feb 21, 2007

I have just recently found that I can do case correction with Excel but I am manually having to do it how can I add it to my macro? The function for doing it does not seem straight forward to me on putting in macro I am sure it is simple but just missing some element of it.

I need to have Proper case for columns C, G and H from rows 11 and down.

View 9 Replies View Related

Use Proper Case Only For Input Box

May 30, 2007

how can i change the text put into an input box into proper case regardless of what my user puts in?

View 9 Replies View Related

Change To Proper Case In VBA

Aug 10, 2009

I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.

If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.

My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.

View 9 Replies View Related

Proper Case Exemptions

Jun 3, 2006

When I asked this question before, I was looking for a way to automate the exemptions on a UserForm. At that time I realized that automation was not a good choice and went with a CommandButton to turn off the Proper case for that entry. I am now trying to do the same thing on a Worksheet change event using this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 1 Or Target.Column > 9 Then Exit Sub
On Error Goto Errhndl
Application.EnableEvents = False
If Target.Column = 1 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 2 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = UCase(Target.Value)
If Target.Column = 4 And Target.Value > "" Then Target.Value = UCase(Left(Target.Value, 2)) & "-" & Right(Target.Value, 2)
If Target.Column = 8 Then Target.Value = UCase(Target.Value)
If Target.Column = 9 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
Errhndl:
Application.EnableEvents = True
End Sub

My problem is with Target.Column = 1. I need a way to disable the proper case for a single row. I tried to use an additional column (J) and place a x in that row, but I could not figure out how to detect if there was anything in that column for the target row.

View 7 Replies View Related

Removing Wording To Get Proper Average?

Jan 16, 2014

I've become stuck on a audit template I'm trying to develop and have spent a few hours to date trying to work it out by looking at forums and videos but with no joy. I've attached a sample template of my work to date

how I can remove a word value within a file so I can get an accurate average - so if the question has a N/A value, I want to remove it so that it doesn't pop up into the final equation when averaging. what the formula for this is? As you can see, both N/A and No give the same answer and have a '0' value when it comes to the summation. What I want is for the NA to not be accounted for and removed when it comes to average the Yes or No only fields.

The second issue is to do with the over all percentage compliance column and is similar to the above. I want this to track the overall percentage but from what I've done to date it works accounting for the NA still and I want this to be also not accounted for.

View 4 Replies View Related

Proper Syntax To Modify COUNTIF With A SUM?

Jul 28, 2013

[Code] ....

U17 = 17

Sum($T$17:$T33) = 33

Result that I am seeking,

[Code] ....

View 5 Replies View Related

Extract Proper Case Words

Aug 17, 2014

I have been using following code to extract all upper case words in a string but the problem is I can not extract words which are proper. For example

This is GOOD

Present output: GOOD
Desired Output: This GOOD

[Code] ....

What can be suitable modification in this case?

View 8 Replies View Related

Converting Worksheet To Proper-Case

Sep 9, 2009

I have an Excel 2003 worksheet with all the data in it in Upper-case.

I found this VBA code which works if you change a cell.

View 6 Replies View Related

Emailing: Proper Copy Of Spreadsheet

Jan 6, 2010

This is a followup to a post I had marked as solved, and it's really not. Below are two different Email routines I've tried, both based on macros by Ron DeBruin, neither of which fully satisfy my need.

One routine creates a copy of a spreadsheet and mails it. The problem with that is that my source spreadsheet has cells containing 255 characters or more, and Excel truncates the cells after 255 characters, so all of the data isn't going into the new spreadsheet.

The other creates a new spreadsheet, copies the data from my old spreadsheet, and pastes it into the new spreadsheet. Problem with that is the new spreadsheet doesn't have my footer or headers and isn't formatted to print correctly.

I need to either find a way to get past the 255 problem, or find a way to copy a properly set up spreadsheet and paste my data into it. My code is below: This one copies the spreadsheet and mails it

View 2 Replies View Related

Proper Formula For Conditional Formatting?

Jun 26, 2014

I have two columns:
Column "D" is a date field
Column "E" is a number field

I would like to Conditional Format the Dates in Column "D" to have a color fill:

If the value in column "E" is >99 and the Date in Column "D" is over 42 days before TODAY

=AND($E$3>99, $D$3

For some reason the end of teh formula will not display:

View 3 Replies View Related

Proper Case Mac, Mc Names In TextBox

Dec 10, 2008

I have a number of textboxes into which I enter the surname of individuals ... at present the textboxes are set to store all names in Uppercase. Is there coding to return names beginning Mc... or Mac... ie McClOUD or MacDONALD, in the more recognized format. I am sure this has been included in the forum but could not find it in a search of the site.

View 4 Replies View Related

Convert To Proper UPPER Procedure

Jul 1, 2006

Convert to Proper UPPER Procedure ...

View 9 Replies View Related

Proper Syntax For Activecell Address

Jul 8, 2006

I want my users to be able to double click on a cell and based upon that cells address or range I want another sheet to be activated. Im currently trying to do something like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

If ActiveCell.Address = ("a10") Then Sheet2.Activate

End Sub

It does not matter to me what specific thing about the currently selected cell is used in the if then statement, but it cannot be the cells value as several cells have the same values. Am I on the right track and just bad syntax or am I way off base here?

View 2 Replies View Related

Proper Case On All Sheets Except Sheet1

Sep 19, 2006

Is it possible to modify this code to exclude the first sheet in the workbook which is called Costs?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("A14:A39")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0

End Sub
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39

''''''''''''''''''''''''''''''''''''''''''''

View 3 Replies View Related

Proper Variable Decleration For Textbox Value

Oct 6, 2006

Can a variable declared as an integer not be assigned to a textbox value?

If not what is the proper way to declare it.

Dim J As Integer
Dim K As Integer
Dim L As Integer
J = TextBox1.Value
K = TextBox2.Value
L=J+K

View 4 Replies View Related

Refine Proper Case Macro

Jan 25, 2007

I am using this macro to ensure that a range of cells appear in Proper Case. However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this. Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub

View 2 Replies View Related

Proper Case Text By Condition

Oct 19, 2007

How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).

There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.

I'm assuming the data was supplied in uppercase

View 9 Replies View Related

Converting Numeric Data To Proper Dates

Jan 4, 2014

I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.

I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.

The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.

View 4 Replies View Related

Transpose Large Data In Proper Format?

Apr 11, 2014

I have huge data

I Want data to be transpose in a below format:-

Emp Code Emp Name Intime Outtime Total worked minutes Extra worked minutes Attendance Shift


And also add the row as per the number of days worked.

VBA code to transpose large number of data.

View 3 Replies View Related

Set Focus Not Redirecting Cursor To Proper Textbox

Jun 18, 2014

I've set a validation within my userform to force the user to enter in a valid employee ID or a general kiosk #. The validation is working, but the setFocus line I have added to place the cursor back in the textbox in the event the value entered was incorrect isn't working. The cursor is moving on to the next textbox making the user have to re-click on the text box in order to correct the invalid entry.

Am I placing my setFocus line in the wrong place of the code? FYI - The second setFocus is working correctly. When the user enters 9999 it directs them to the IT Ticket number text box. Prior to me moving the textbox, the cursor would jump all other text boxes to allow the user to enter in a ticket number within the ticket number box after entering 9999 within the PERNR text box.

View 2 Replies View Related

Change Case Of Text To Proper Except For Prepositions

Oct 17, 2012

macro that will change the case of a string of texts to proper except for prepositions (e.g. and, or, about, the, etc.).

View 2 Replies View Related

Proper Way To Replace And Delete Embedded Object?

Oct 25, 2012

what I am doing is running a parameter query. Based on the users selection it pulls in and embeds a pdf file based on a link put into cell A1. The problem is when the user selects another file. My current process deletes all of the oleobjects on the page (because I couldnt figure out how to just delete this pdf embedded object as it is named differently each time it is pulled in) My current process worked fine up until I needed to add some controls (radio buttons) now when it loads the next file it deletes the radio buttons. Is there a way to name this embedded pdf file so that I can then delete only that object? or is there a fundamental better way to handle this?

below is the section in question

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
wsh.OLEObjects.Delete
Next wsh
ActiveCell.Offset(0, 0).Range("j1").Select
ActiveSheet.OLEObjects.Add(Filename:=Range("a1").Value, Link:= _
False, DisplayAsIcon:=False).Select

View 3 Replies View Related

Prevent Proper Case Of Letters Following A Comma

Mar 17, 2007

When using the PROPER function, it capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, and converts all other letters to lowercase.

However, if A1 contains the text "2-cent's worth"; then =PROPER(A1) will return the following result: "2-Cent'S Worth".

Is there a way to prevent the PROPER function from capitalizing the first letter following the apostrophe?

View 9 Replies View Related

How Can I Apply Thr PROPER Function To An Entire Sheet

Nov 14, 2007

I currently use the PROPER function for one column at a time. How can I apply it to a whole worksheet, preferably without creating new columns?

View 9 Replies View Related

Code Is Not Inserting Proper Link In Email

Jul 16, 2009

In the Excel file I am trying to create I have a code that is sending an email notification that a file is ready for review. In that notification I want to insert a link to the file the email is referring to. No files are attached to the email.

Anyway, the code I am using is not pasting the link properly. This is what is pasted into the email body:

file://C:Test4DarrenRRR09-0001 DARREN CORP.xls (this link does not work)

The link should be appearing something like:

file://C:Test4DarrenRRR09-0001 DARREN CORP.xls

I can't seem to alter the code to do what I want. When I hover over the filename while stepping through my code everything look correct so I think it must be the line of code below that requires altering....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved