Simple Code In 'Private Sub'

Jan 8, 2008

I usually create fairly simple macros using 'button' feature. However, now I'm using the 'CommandButton', as I had to make buttons highlighted once selected - So I have a choice of five options, whichever the user chooses that option background changes, the others stay grey...anyway, thats the fancy-editing done, but now when i enter a simple instruction - hide rows function - I get the error message "Select method of range class failed"

Here is the full code - like i say it works fine when its just the editing, but when i try to actually hide rows it doesn't work. - I will also add that the code is inside 'Microsoft Excel Objects' and further 'Sheet 1 (INDEX)' - not a module - I'm not sure if this is important.

Private Sub JackStart_click()

JackStart.BackColor = 32896
JackStart.ForeColor = 16777215
JackStart.Font.Bold = True
Majix.BackColor = 8421504
Majix.ForeColor = 0
Majix.Font.Bold = False

Sheets("Bank Reconciliation").Select
Rows("3:3").Select
Selection.EntireRow.Hidden = True

Sheets("INDEX").Select
Range("A1").Select

End Sub

View 9 Replies


ADVERTISEMENT

Simple Private Sub Not Working?

May 7, 2012

Here is my simple Private Sub that I can't get to work,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target

[Code]....

I have a checkbox control that changes the value to "True" or "False" in column 4. Here lies the issue, if I use the check box control to change the value, the private sub does not work. If i type the value of "True" or "False" where the check box control stores its value, the private sub works. I would like to see if there is way to get the private sub to work by simply clicking on the check box control only.

View 9 Replies View Related

Code Fails When Called From Private Sub

Aug 22, 2007

I'm having a hard time with this piece of

Private Sub Assign1Combo_Change()
If Range("ComboVisible") = False Then Exit Sub
Assign1_Download
End Sub

Symptoms:
- As soon as this is used, Excel/VBA can't select any range on the worksheet. For example, the following code (within the sub Assign1_Download) no longer works:

Range("firstdata").Select

Excel/VBA doesn't select that Named Range or any other range I try (ex. A1). - This problem only happens in Excel 2003. In Excel 2002 everything works fine (can make any selection).

View 5 Replies View Related

Call Private Macro But Do Not Copy Code To New File

Apr 27, 2012

I would like to have something that will auto run the code in Module1 when the Excel File is opened, but I do not want to code tansfered into the file when it is saved in its location.

I don't mind if the code is tranfered to saved file if in fact I can make sure the code is not run when the new file is opened...

Everything is working as intended at the moment, but when openeing the saved file the entire macro tried to run again. This is what I do not want...

I currently have the following code in Module1.

Private Sub Workbook_Open()
'
' Format_Copy_&_Paste
'
Application.OnKey "^+r", "Warehouse_Cost_Summary"
'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DataSet"

[Code] ..........

View 4 Replies View Related

FOR, IF, NEXT... Error In VBA While Attempting A Simple Code

Jan 9, 2009

Had a bit of code I was working on for a guy on here, and came across a problem while putting the finishing touches on it. It's since been solved by someone else, but for future reference...

View 13 Replies View Related

Code For Simple Checkbox On User Form.

Jan 21, 2010

I really don't know how to code a checkbox correctly. On the sheet, I have a form with CheckBox1 and Checkbox2. I would like Checkbox2 disabled until Checkbox1 is checked. Also, when Checkbox1 is checked, display Image1, otherwise Image1 is hidden. When CheckBox2 is enabled and checked, display Image2, otherwise Image2 is hidden. I know this is real beginner stuff, I'm still a rookie.

View 4 Replies View Related

Running Simple VBA Code Causing Screen Errors

Nov 30, 2009

I have a rather large workbook (30 sheets, 10MB) that has one worksheet with many INDIRECT functions in it (pulling data from the same file, different tabs). I am working to put simple code into the workbook to protect and/or unprotect all worksheets. I have gotten code to work to both protect and unprotect all the sheets, but when I run the unprotect code (see below), and then I go to edit the workbook, data from the sheet with many "indirect" functions temporarily "overwrites" the data on the active sheet (this is fixed when I scroll my mouse over the effected cells). I am developing this workbook for other users, so I'd like to fix this before sending it off to them.

This problem does not happen until after I run the following .....

View 14 Replies View Related

Simple Color Code Not Working On Specific Sheet

Jul 22, 2013

This very simple color code below worked before but now it gives "Application-defined or object-defined error". When I try it on a new workbook, it works fine.

Code:
Sub sdsdfsd()
Selection.Interior.Color = 65535
End Sub

View 1 Replies View Related

This Simple 10 Line Code Doesn't Work If Just The Reference Is Changed..

Jun 10, 2009

I asked for a macro to delete the whole row if a duplicate customer number was found in column B. Sometimes, though, my column numbers change. So, logically thinking, I simply changed the criteria, but the macro ONLY seems to work if duplicate customer numbers are in column B only.

This code below won't work if the Customer Number is in column D instead of B even if the reference of B:B is changed to D:D, it doesn't carry the macro over.?

View 3 Replies View Related

Simple VBA "View Code"

Oct 31, 2007

Basically in my sheet I have a table of various data, where an entrant can appear more than once. e.g.

ACBDC
AABDC
DBCCD
BCDAB

I want to make it so that if you type in an input cell (say cell G10) an entrant your interest in (i.e. A, B, C or D), then the corresponding entrants in the table will change font color to red and go bold.

I know this must be done by right clicking on the sheet and entering a code in "View code". I can't use conditional formatting because I've already used up all 3 conditions!

View 9 Replies View Related

Two Private Sub

Apr 29, 2007

IN SAME SHEET.
1. IS TO RESTRICT CELL POINTER. EG. HIT {ENTER} : COL(1) TO COL(5)
2. IS TO RUN PROCEDURE WHEN DATA IS INPUTTED IN COL(3)
THESE TWO SUB CAN RUN IN ONE SHEET ?

Private Sub Worksheet_Change(ByVal Target As Range) 'SHEET1
ActiveCell.Offset(0, 0).Activate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'WORKBOOK
Application.OnKey "{ENTER}"
Application.OnKey "~"
End Sub

View 5 Replies View Related

How To Use 2 Private Sub Worksheet_Change

Jul 10, 2013

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.RowHeight = 16
End Sub

[Code]....

How to Use 2 Private Sub Worksheet_Change

View 6 Replies View Related

Private Sub Running Twice

Oct 22, 2013

I have a drop down box selecting from a list of dates (Oct-13 - Dec-14) that I need to display as "mmm-yy" to the end user so have written the following code to format when a date is selected:

Code:
Private Sub SDatePicker_Change()
SDatePicker.Value = Format(SDatePicker.Value, "mmm-yy")
End Sub

For some reason the code seems to run through itself twice and I can't figure out why. The result of this is an incorrect date being displayed (strangely when I select 'Jan-14' for example, the result is 'Jan-13'.

Perhaps I should point out that the default formatting of the date values seems to be in number format e.g. 41976.

View 3 Replies View Related

VBA To Add Private Sub To Sheet?

Jun 15, 2014

I have VBA script to format a csv.

As part of the formatting, i want to add a private sub to the activesheet that will enable a colour-on-click function.

How can I make the overall VBA script insert of the privatesub into Sheet1?

View 2 Replies View Related

Difference Between Private Sub And Sub

May 30, 2007

I have two funtions which I am trying to put in ThisWorkbook.

Private Sub Workbook_Open and Private Sub 2. The Workbook_Open calls on Sub 2.

Now, with both of these in ThisWorkbook, I get the error that Sub 2 macro cannot be found.

And if I put the Sub 2 in a module, everything works.

Now, I am trying to put both in ThisWorkbook instead of only one.

View 9 Replies View Related

Two Private Sub Worksheet_Change

May 22, 2008

I currently have this code in my sheet, and would like to incorporate the second bit of code into the same sheet, but not sure how to do it. At this point, when I just put them together neither will work.


Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column 12 Then Exit Sub

If Target.Value = "Daniel Amaya" Then
Target.EntireRow.Cut
Sheets("Daniel").Range("A3").End(xlUp).Offset(1, 0).EntireRow.Insert
Target.EntireRow.Delete

View 9 Replies View Related

Getting A Private Sub To Automatically Run

Sep 2, 2008

My Workbook contains 12 worksheets and in each Worksheet there is a drop down list. The code for each one is in the worksheet code section where they are Private Subs.

What i want is that once the work book opens it will run the code in each of the work sheets instead of going into each bit of code and running it manually.

View 9 Replies View Related

Private Sub Worksheet_Activate

Oct 8, 2008

the below macro works when the worksheet name is Sheet1, but as soon as i change the worksheet to Rec it does not work, what have i done wrong?


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Sheet1 Then
If Sheet1.AutoFilterMode Then
If Sheet1.FilterMode Then Sheet1.ShowAllData
End If
End If
End Sub

Does not work


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Rec Then
If Rec.AutoFilterMode Then
If Rec.FilterMode Then Rec.ShowAllData
End If
End If
End Sub

View 9 Replies View Related

Pt Private Infront Of Sub

Jan 13, 2009

My nacro wont work if I put private infront of sub, if I take out private it works. Its not connected to any object, just simple copy paste

View 9 Replies View Related

Running Private Sub From A Macro

Mar 17, 2014

I have 3 different sheets with a private sub on each all labelled

Private Sub CommandButton1_Click()

The macro's runs fine on each page.

I want to put a macro on a separate sheet that i can run each of those macro's

I did initially copy the original private macro and change the

Private Sub CommandButton1_Click()

to

sub report()

But I couldn't do all 3 in the same manner. i did change each name to something different... the other 2 subs did run, but they didn't do anything except put the massage box on the end saying that "the macro has finished".

The private macros are on sheets 26, 28 and 12.

View 3 Replies View Related

Private Sub Worksheet Error

Feb 24, 2009

im getting run time error 40036 and i dont know how to correct it here is the code

View 9 Replies View Related

Call A Private Sub From Another Module

Aug 13, 2009

Is it possible to call a Private Sub from another Module?

View 2 Replies View Related

Place File Name Into Private Sub

Dec 11, 2009

The code below writes the names of all active worksheets into the A column of the active worksheet. I have a situation where I have 2 workbooks open (3 if you count PERSONAL.XLS). One of the worksheets has a consistent name, the other has a name that changes every week (not in a consistent pattern).

I'd like to be able to use the routine below to get a list of open workbooks, ignore the one I'm working in and PERSONAL.XLS, and instead put the remaining filename into other Private Subs in a User Form to run other routines.

View 6 Replies View Related

Private Sub To Insert Formula

Sep 7, 2013

Private Sub to do the following:

'Add Formula to column "G" of any active row.
Range. Rows 8 thru 9999

If any value is input into column "C" and/or column "F" of the active row
Then Insert and activate Formula "=c*f" into column "G" of that row.

View 9 Replies View Related

Private Sub Worksheet_Change (timer)

May 13, 2008

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$b$1" Then

For i = 1 To 10
Cells(1, 1) = i
Next i

End If

End Sub

It is supposed to count to 10 when the info in cell B1 changes, i would like to use it as a timer.

View 9 Replies View Related

Delete Worksheet Private Sub After Paste

Mar 20, 2014

I have a worksheet with a bunch of formulas in it. Computations are done on the worksheet and then I start a macro that copies the sheet and replaces the calculations with values.

The worksheet has a sheet level private sub.

View 6 Replies View Related

Prevent Private Sub Macro From Running

Nov 14, 2008

I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.

View 6 Replies View Related

Passing A Private Variable To Another Module

Nov 25, 2008

I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.

View 8 Replies View Related

Passing Variable Value From Private Sub To Module

Aug 12, 2009

I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work.

Private Sub Code as follows:

View 3 Replies View Related

Private Sub And Worksheet Protection With Password

Jan 24, 2010

All my macros in Module 1 work OK when I password protect my Excel 2003 worksheet named: Data. The only code that does not work when I password protect my worksheet is the Private Sub shown below. If I do not use a password, it works OK. As soon as I enter a password to protect my worksheet, the code below no longer works.

In ThisWorkbook, the following code shows the password to be password. In reality, there is a different password.

View 10 Replies View Related







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