I got two worksheets visible for user to enter information with vba codes behind the sheets(i.e. Private Sub Worksheet_SelectionChange(ByVal Target As Range). Most cells of the worksheets are protected if the vba is executed. However, it was found that if user chooses not to run the macro when prompted, the worksheet can be edited whatever user want. So, how can the worksheet whole worksheet is protected if macro is not run?
I have a query in Excel 2003.
My question is:
I have a macro created for my Excel. I have a short cut key to run this macro. When i run the macro, the VB editor is opened where i have written the code. My requirement is, I don't want the VB editor to be opened when i run the macro. I want the macro to be executed but the VB editor should not be visible to the user.
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down.
My drop-down list is created through "Data->Validation->List box"
I have a macro that looks at a list of students and the school they attend, and then creates an individual worksheet for each school containing only their students. I want to hide Column B on each of those worksheets. This seems like it should be easy, but it is not working.
When I leave it as is pasted below, Column B does not get hidden.
When I change it to Sheets(school.Value).Columns("B").Select I get an error message stating "Select method of Range class failed."
Sub ExtractSchools()
Dim wsTransfer As Worksheet 'worksheet with transferred data from registrations wrkbk
Dim wsList As Worksheet 'worksheet with list of students
Dim wsNew As Worksheet 'worksheet being added for a school
Dim wSheet As Worksheet 'name to loop through all worksheets
Dim rng As Range
Dim school As Range
Dim rowNum As Integer
Set wsTransfer = Sheets("Transfer")
Set wsList = Sheets("Student List")
wsTransfer.Select
Range("Database_Transfer").AdvancedFilter xlFilterCopy, Range("Criteria"), _
wsList.Range("Database_Unique").........................................
Getting a macro to work. I've looked through the forums pretty extensively but ad I'm not too hot with the old vba, I haven't been able to get it working.
I have two worksheets in the same workbook. The first worksheet, let's call it Input, is one for data entry; and the second one, let's call in Output, is formatted for printing.
There are 8 drop down boxes from a data validation list, that when a particular option or three are selected, I need the Output worksheet to unhide only the rows associated with those options.
In trying to get this all to work, I'd added a function in the cell to the left of each option in the Output page that will show the text "show" when that option is selected on the Input page, or the text "hide" if not selected.
I think I could do this with some time with a clunky and long macro, but would prefer to us some kind of "for each" option to hide rows that have "hide" shown in column A, as I'm looking at a range of 100~ cells.
I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.
I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?
I have some code which formats a worksheet based on user selections in a form. The code works, except when the user has made the same selections in two multi-select list boxes. I need to find a way to determine whether the code has already formatted the worksheet.
To me, it seems that there might be two ways to do this, 1) have a test on the original block of code that checks whether this portion of the code has already executed for this variable or 2) check the worksheet for the string (the code formats the same string each time but adds the variable for the current iteration of the loop only if Year1 = True.) The problem is that I only want to format the column if it hasn't already been formatted for that selection.
If CostShare = True Then
For Each ctrl In UserForm2.Controls
If TypeName(ctrl) = " ComboBox" And ctrl.Visible = True Then
If ctrl.Value = "Cost-Share" Then
t = ctrl.Tag
For Each ctrl2 In UserForm2.Controls
If TypeName(ctrl2) = "ListBox" Then
If ctrl2.Tag = t Then
With ctrl2
For ii = 0 To .ListCount - 1...............................
I'm trying to identify the Active Cell for the formula that is executing.
Currently, using VBA, if the last manually clicked active cell (where the cursor resides)
is different than the cell containing the executing formula I get the
wrong cell, row, or column using ActiveCell.
I have just encountered a very peculiar problem when using Scrollbar_Change Event for Controls Scrollbar (in a worksheet). I have tree scrollbars and assigned Event code for each one: ScrollBar1_Change, ScrollBar2_Change and ScrollBar3_Change.
Events works perfectly when I click on the arrows to adjust the scrollbar. What is strage however, event is not executed when I adjust the scrollbar itself (pulling the bar with a mouse) if I try it first time after adjusting another scrollbar. In such situation Scrollbar is adjusted on the screen, even linked cell is changed, but the Change Event is not executed. But when the same scrollbar is adjusted second time Change event is executed. To sum up: Scrollbar_Change event is not executed when adjusting the scrollbar with the mouse the first time after "switching" from one scrollbar to another, but is works perfectly in any other situation. It has nothing to do with the code istelf. I get this effect when I create a new workbookm add 3 scrollbars and a code like:
Private Sub ScrollBar1_Change()
MsgBox "ScrollBar1 changed!"
End Sub
Private Sub ScrollBar2_Change()
MsgBox "ScrollBar2 changed!"
End Sub
Private Sub ScrollBar3_Change()
MsgBox "ScrollBar3 changed!"
End Sub
What may be the cause of this selective "disobedience"? Note also, that I get this error on Excel 2000 (not tested it on Excel XP or 2003).
I ran into a screen update problem in a project I was working on and couldn't find a solution, so I wrote some test code in a new workbook as follows;
Sub Flkr()
'-------------------------
'Screen update test
'-------------------------
'Select sheet1
Sheets("Sheet1").Select
'Disable screen update
Application.ScreenUpdating = False
'Select sheet2
Sheets("Sheet2").Select
'display message
MsgBox "Why is the %&$@ screen updating?", vbCritical, "??????"
End Sub
Seems simple enough,eh?
When I step through this code the first statement works(sheet1 is selected)
The next statement is executed without an error, so I assume it works
The next statement sheet2 is selected, and the screen updates!
i want to know how to show the progress of code exceuted in an excel sheet. like when we download a file from web or server it shows the staus of the file transfer.
View 2 Replies View RelatedWhen I close my worksheet excel asks me if I wish to save the file one last time, if I click 'yes' I would like some more code to be executed and then have the spreadsheet save itself and close definitively, how can this be done using vba?
View 9 Replies View RelatedI have a Form that user fill in information on, once they click the OK button, excel is filled with the inserted text on the form in the correct cells.
My question is, how do I validate that they have actually entered data in some cells, which I want to make mandatory, and if they have not, prompt them (this could be a simple message box) to fill it in. Let them fill it, and once they click OK again, check again... until all the mandatory fields are filled, only then will the macro fill in the excel cells.
i have this code which askes the user for a job number once the workbook is opened.
Private Sub Workbook_open()
' If sheet was named by original open routine, exit
If ActiveSheet.Name = "Main Roof" Then Exit Sub
' otherwise
Do
Returnvalue = InputBox("Please Enter a New Job Number.", "Information")
' Allow changes by entering q as the Job Number
If Returnvalue = "q" Then Exit Sub
' Delete the ' from the front of the following two lines and
' then when you enter q as the Job Number you will also be
' asked for a password. The default password is toe.
what iam trying to do is get it to open the userform "WorkSelection" after it has completed the above code.
I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
I was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?
Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub
I have inherited support for the an Excel 'program' that seems to be fairly unstable. I am currently having problems with the following code in a subroutine (executed when the user presses a command button):
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
.PrintArea = ActiveSheet.UsedRange
End With
Sometimes it works and sometimes I get the following error: Run-time error '1004'
Unable to set the PrintArea property of the PageSetup class.
… on the .PrintArea = ActiveSheet.UsedRange
I am having a hard time figuring out what is causing its wishy-washiness. Running the following code (launched from worksheet named 'consumer' with a command button):
Sub subConsumerFacesheet()...................
I want activation of userform to hide worksheet, but as soon as the Userform is closed, the worksheet should show.
I have attached file to aid.
How to hide a worksheet? ... if I hide it will I it still be able to copy it?
View 6 Replies View RelatedI want to know how do I hide my "Raw Data" worksheets? I have about 10 worksheets in a workbook and just need to hide the first one - where all the raw data is store...
We're trying to create an invoicing sheet with MS excel for our sub contractors but we also want the information they input to be transfered onto a second work sheet within the work book that has our mark up added.
Problem is that we don't want our sub contractors to see the sheet with the mark up. Is there any way to hide a work sheet or to limit access to the work sheets some one can veiw with in a workbook?
I have an excel workbook that has multiple worksheets that is linked to our in-house system via ODBC and it refreshes every 5 minutes with up to date sales data.
I am looking to output just one of the work sheets onto a large LCD display / wallboard.
I need to just display the summary worksheet without the excel toolbars / gui
I put the correct cell in place $K$23, now I want to hide a worksheet called "DutyCode" when info!$k$23 is blank or has "xx" or "XX" in the cell, and then unhide "DutyCode" when the number 27 is inputed in info!$k$23
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$k$23" Then Exit Sub
If Target.Value = 1234 Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
End Sub
I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.
I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.
I don't know exactly if I should use a checkbox, listbox, combo box ect.
In excel I would like to create a macro which wil be able to hide all lines were a text (to be defined in a cell for example) is not present
Here is an example:
linesdata:X#1#2a#3b#4b#5a#6a#7c
Pending X value lines #2 to #7 will be hidden or not
How do you hide a sheet?
View 6 Replies View RelatedI've done a search for this but cant find the complete answer for what I'm looking for. Basically I've got a "Home Sheet" set-up where the user can only navigate to worksheets via the buttons on the home page (with a short- cut to go back to the home page). It works ok (very basic though) but the user can still use the worksheet tabs at the bottom of the screen to navigate (making this system void).
how I can, either, hide the worksheet tabs at the bottom of excel, or, a better way of navigating these worksheets (there is only about 6 of them).
To hide a worksheet upon open I use: wsSheet.Visible = xlSheetVeryHidden. What do I use to hide a chart which is its own worksheet?
View 3 Replies View Relatedknew if there was a way to hide the available tabs in an excel worksheet.
I know you may be thinking just right click and hide, however what I am looking for is a little different.
Basically I have a Macro Enabled work sheet which works on a "one click" system. The work sheet is full of buttons with assigned macros and data validation drop down boxes which link to many different tabs. The entire sheet is for information only so I will be locking the sheets to avoid data manipulation. The problem with right clicking and hiding the tabs is that it interfears with the macros.
All I really want to do is make these tabs not visible. I have set the sheet to open in full screen mode and they obviously are still there, but this is basically what I am looking to do. Not so much hide them in terms of sending them into the background, more so make them "drop off" the bottom of the screen to they are still active but not visible.
I am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.
View 4 Replies View Related