Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Event Keypress, Tab Or Keydown And Setfocus


How to setfocus on a cell, when user press enter, key up, or tab ?
examp. :

i want setfocus to cell A5, when i press enter or key up on cell A2.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Keydown To Effect To All Controls That Support Event
Is there a way to fix a private sub to catch keydown effect of various combos and textboxes of my userform?

Instead of typing the same thing to each and every control's keydown event just call a sub. Code goes like:

Select Case KeyCode
Case 112
ShellExecute 0, "open", "hh.exe", ThisWorkbook.Path & "1.chm", "", 1
Case 113
If MsgBox("Backup now", vbQuestion + vbYesNo) = vbYes Then BackUp
Case 114
Col = 3
SearchD
Case 115
showexcel
Case 116
readonly
e.c.t.

Instead of that, if i place code in a private sub test() just call test in keydown event

View Replies!   View Related
Writing Function For Textbox Keypress Event
I have got some code that is duplacated a few times. It allows only number values to be entered into a textbox.

Is there a way to make a function out of this to cut out the duplication of code.

View Replies!   View Related
Beep From KeyPress
I've got a small - almost "cosmetic" problem. I have a listBox on a userForm and I'm checking for a keystroke combination to see if the user wants to edit related data. That's working fine. However, when I return to the userForm, it beeps at me. Impertinent! I think it's because Excel doesn't know what to do with the keyStroke that I used - Alt U. I've attached code. I think that I just have to set the keycode to something that will be ignored by Excel.

View Replies!   View Related
Limit KeyPress To Text Only
Iv found some code that will limit my textbox entry to numbers only, i dont quite understand how it works and i need to change it so it limits the textbox keypress entry to Text Values only.

View Replies!   View Related
Get 2 Textboxes To Display Different Strings Based On Keypress
I have a Userform whose purpose it is to capture payment information, in particular Creditcard information. To this end it has (amongst other elements) 2 textboxes - textboxCardNr1 and TextboxCardnr2.

the User enters the cardnr in Textboxcardnr1 as, e.g. 4321098765432109. Through the code below I transform this into the more readable number
4321-0987-6543-2109. I capture the keypress instance to only allow numbers to be entered in this field and to simultaniously update TextboxCardNr2 with the keystrokes.

On exiting TextboxCardNr1 the content of TextboxCardNr2 is copied to the clipboard to be available for pasting on a secure website in the approriate field.

My problem is, that when the user makes a mistake and changes the number in TextboxCardNr1, those changes are not automatically mirrored in TextboxCardNr2.

I want the easy to read form of the card - with the dashes, to be saved. The website the user is pasting the content of TextboxCardnr2 to does not accept the number with dashes.

Private Sub TextBoxCardNr1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim taste As String
Dim wert As String

taste = VBA.Chr(KeyAscii)
wert = Me.TextBoxCardNr1.Text

KeyAscii = 0
If taste = "0" Or taste = "1" Or taste = "2" Or taste = "3" Or taste = "4" Or taste = "5" Or taste = "6" Or taste = "7" Or taste = "8" Or taste = "9" Then If Not Me.TextBoxCardNr2.Text Like "################" Then
Me.TextBoxCardNr2.Text = Me.TextBoxCardNr2.Text & taste............................

View Replies!   View Related
SetFocus On TextBox
I have the below to check for a numeric entry and when it's not, it deletes the entry, shows a Splash Form for a few seconds telling the user to enter Numeric, and then unloads.

The problem is, on the SetFocus part. I thought that it should put the cursor back into TextBox2 for another entry. It doesn't, in fact the cursor is nowhere to be seen. I have to grab the mouse and click in the TB2 to reenter a number.

Private Sub TextBox2_Change()
'//Check for numeric entry
If Not IsNumeric(TextBox2) Then
Me.TextBox2 = ""
'//Show SplashForm if not numeric
SplashForm.Show
Me.TextBox2.SetFocus
End If

End Sub

View Replies!   View Related
Setfocus In Userform Not Working
I'm having some problems with SetFocus on a user form that I am using. Code is Below.

Dim Answer As Variant
With cboAccount
If .Value "" And .ListIndex = -1 Then
Answer = MsgBox(cboAccount.Value & " is not a registered account, would you like to add it?", vbYesNo)
If Answer = vbYes Then
Load frmNewAccount
frmNewAccount.txtAccountName.Value = frmEnterTransaction.cboAccount.Value
frmNewAccount.Show
If Answer = vbNo Then
cboAccount.SetFocus
End If
End If
End If
End With
End Sub

basically the code asks if the if the answer placed in a cboAccount (combo box) is valid against the list designated to that combo box,

If the entry is not valid a message box appears asking if they would like to add the entry to the valid list if they do not want to do this they can click no and in which case I wan't the focus to be set back to the combo box however currently the setfocus command above does not does not work and the focus is set to the next text box.


View Replies!   View Related
Control Events And SetFocus
I have been working for 8-1/2 hours to get the focus to move to a specified control on my user form if criteria are met in two other controls on the same user form. It's not responding as I expect. I think I know what is happening; I just don't know why.

Situation: I have a user form with the following relevant fields in this tab order - LMonthDay, tbMonthDay, SBMonthDay, LYear, tbYear, SBYear, LTime, and tbTime. I used the designations L, tb, and SB in my control names to represent label, text box, and spin button respectively.

Problem: Once the SBMonthDay control has the focus, if I press TAB without changing the control's value, it does not execute the Exit event statement the way I anticipated. I want the focus to move to the tbTime control when the tbMonthDay & tbYear controls are already properly populated. (They will already be populated for 99% of the entries in this case.)

Private Sub SBMonthDay_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Debug.Print "SBMonthDay_Exit Begin"
If ISLIKE(tbMonthDay.text, "####") _
And ISLIKE(tbYear.text, "####") Then
tbTime.SetFocus
Else: tbYear.SetFocus
End If
Debug.Print "SBMonthDay_Exit End"
End Sub ...........................................

View Replies!   View Related
TextBox.SetFocus Not Working
if i write the following function on a checkbox,

View Replies!   View Related
Setfocus To Combobox On Multipage
I have a userform with a multipage consisting of 4 tabs.

Each one has several combobox's and 1 commandbutton.

As you move through the form the commandbutton moves the data to my worksheet then moves you to the next page. When it reaches the last page it will return you to the first page then unload the form which is great.

The problem I am having is the cursor is not in any of the boxes on the first page. If I click on one of the other tabs then return to first page the first combobox has the focus. All the tab orders are correct. Can anyone point me in the right direction.

Private Sub cmdNext3_Click()
ActiveWorkbook.Sheets("CarDetails").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

View Replies!   View Related
Using SetFocus To Any Other Object On Userform
I have been trying to setfocus to any other object on my userform with absolutely no luck at all?

In the code below I open a dialog box to import a text file (with CommandButton1), if no file is selected the user is prompted, this works fine. However it leaves focus to the textbox and the user would have to click on some other object and re-enter the textbox to open the dialog box again.

View Replies!   View Related
Setfocus + Show A Page
Excel 2003. User form coding an If statement the "Else" FieldName.Setfocus. The User form has a MultiPage object in addition to the above (setfocus) in a particular field (NOT on the MultiPage object) I want it open to Page2 instead of what ever page it was last closed on. I tried this: MultiPage1.Page2.Enabled

View Replies!   View Related
TextBox Setfocus Unexpected Call
I've got a user form with 12 textbox, one for each month a total textbox number 13 and a 14th text box (TB28) to enter $'s in.

AfterUpdate in each textbox 1-12 posts its value to a worksheet all values are summed in a =sum range which in turn populates textbox 13. When TB13.text = 100.00 it sucessfully calls a number of routines but what I can't get it to do is set focus on TB28.

Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With FormPhasedAmt.TextBox28
.SelStart = 0
.SelLength = Len(TextBox28.Text)
.SetFocus
End With
End If
End Sub

It returns a Run time error Unexpected call to method or property access highlighting .SetFocus. The rest of the code works OK. Is it because other textbox on the same user form have text highlighted because of the tab order?

View Replies!   View Related
SetFocus On TextBox As First Control In A Frame
In Excel 2000 there seems to be a problem with setting the focus in a UserForm TextBox if the SetFocus method is applied in the same submodule as the UserForm.Show method. Microsoft's circumvention for this is to put the SetFocus command in the form's Activate event submodule.

This seems to work OK except when the TextBox you are applying the SetFocus to is the first control inside a frame. It works if the TextBox is not the first control, and it can be circumvented by first setting the focus on a subsequent control then switching the focus to the intended control.

However, the circumvention is not that useful if there is only one TextBox control in a frame or, as in my current project, if you try to create a generic piece of code to validate controls from multiple forms and set the focus from within the generic code.

Does anyone know of a way over overcoming this problem and being able to directly set the focus to the first TextBox inside a frame?

View Replies!   View Related
Setfocus (point To A Cell In A Spreadsheet)
I would like point to a cell in a spreadsheet, click a Command Button and copy the content of this cell to another cell, say D30.

View Replies!   View Related
Setfocus From Module To Userform Control
I have a project with many textboxes on different forms. On some of the textbox_exits on some of the forms, I call a standard module that checks what the user has input. If the user needs to change the input, a msgbox appears to inform the user the info needs to be changed.

I have tried to use ControlSource, Name etc., But I cannot seem to setfocus back onto which ever textbox the input needs to be changed. I think it has something to do wih the _exit event, but not sure.

View Replies!   View Related
SetFocus On Multi Page UserForm
I've got a userform with multiple pages, and in my userform initialize sub I set the focus to "combobox1" on "page1". When a user is on page2 of the form and clicks my "ClearData" button, which calls the initialize event, I get a run time error 2110 - "Can't move focus... etc" I would like users to have the ability to clear the form and start over no matter what page they are on.

View Replies!   View Related
Inscribing Cells: Run A Macro On Enter Keypress, That Would Execute Different Code Depending On That "inscription" That Would Be Invisible To User
Is there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:

Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub

The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.

View Replies!   View Related
Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?

View Replies!   View Related
Change Event Causing Event To Fire Again
I have the following code, which works perfectly:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TotalDays As Integer

TotalDays = Range("C65536").End(xlUp).Row + 1

The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.

However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.

How do I add the following, to my previous code?

Range("E8").Value = Cells(7, 6) * 2.5

View Replies!   View Related
Event Name
What would be the name of the event where if I select a particular cell in Sheet1 it triggers something in say Sheet2?

View Replies!   View Related
Hyperlink Event
I have a hyperlink within Sheet1 (Functionalities) of my workbook that looks like this: ...

View Replies!   View Related
Before Save Event
I want to execute as the user saves the workbook. I want to unhide the rows that may have been hidden during use, on the save. I would prefer it to just happen with no interaction with the user. They save the the book and without them even knowing the rows are unhidden and the file saves. The code it self works as I want it to, I added to a command button with no problems as soon as I add it to the before save in the Thisworkbook it will not even work even if I just try to step in.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets("Prelims").Range("A11:A511").EntireRow.Hidden = False
Worksheets("Elecs").Range("A11:A1261").EntireRow.Hidden = False
Worksheets("Civils").Range("A11:A5011").EntireRow.Hidden = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Worksheets("Civils").Select
End Sub


View Replies!   View Related
Run Before Close Event
I have a MS Access db in which I have two delete queries called 'qrySessionsCompleted1' and 'qrySessionsCompleted2'. What VBA code can I use in the BeforeClose event to run these?

View Replies!   View Related
Event Status
I have a worksheet that I use to track course scheduling. I have 5 columns which contain dates. At the end I have a Event Status cell that i manually update based on which of my 6 date fields are filled in. I would like to have the Event Status automatically fill with a word as I add dates to the 5 columns. If there is a date in columns 1,2,3, and 4 the event status cell would update based on the date in column 4, dates in columns 1 and 2 - the event status would update based on column 2, etc.

Here are my column headers and what the event status says if there is a date in that column, and no dates in any column to the right of it.

Notional Start Date - Not Contacted
Declined Date - Declined
Contacted Date - Contacted/Working
Scheduled Date - Scheduled
Completion Date - Completed

View Replies!   View Related
AfterCalculate Event
I have a macro that needs to run whenever a result from a calculation on my worksheet changes. I'm currently using Worksheet_Change to accomplish this, which works fine.

The only problem is that I would like the macro to wait until all calculations are finished (there's a bunch...) before running the macro. It would seem that the AfterCalculate event would help, but I can't seem to get the code to work correctly (or even at all). I'm pretty new at this... If anyone can give me a clue, I would greatly appreciate it! My searches on the message board and the web have come up pretty empty.

I'm running Excel 2007. Not sure if any other details are needed.

View Replies!   View Related
On Selection Event
I would like to create some VBA code that changes the color of the cells I have selected, as soon as I let go of my mouse click. Additionally, is there a way to identify where the range begins and ends so I can test to make sure the range is inside a certain area?

View Replies!   View Related
Worksheet Event
I would like to run a some code that is only activated if another worksheet in the same workbook is selected and I'm not sure how to achieve it.

View Replies!   View Related
Worksheet_SelectionChange Event
I found this code on one of my many searches, that works great.

(can't remember where I got it or who wrote it, (My deepest apoligies to the author))

Public pRule

Sub butRulerToggle_Click()
pRule = Not pRule
Selection.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If pRule Then
For Each aCell In ActiveSheet.UsedRange
If aCell.Interior.ColorIndex = 27 Then aCell.Interior.ColorIndex = xlNone
Next
On Error Resume Next
For Each aCell In Application.Intersect(ActiveCell.EntireRow.Cells, ActiveSheet.UsedRange)
If aCell.Interior.ColorIndex = xlNone Then aCell.Interior.ColorIndex = 27
Next
End If
End Sub

My question:
This works in a sheet module. How can I put it in the This Workbook module so it works on all sheets.

By the way, what this does is highlight the whole row on a clik of a cell, leaving any color formating that was initially there alone.

Very useful if you are looking at say A10 and then want to look at Z10 without losing focus on the row.

View Replies!   View Related
Tab Key Change Event
I have a user input box (VBA) with two columns of data entry. The leftmost column has text boxes labeled color1, color2, color3, etc.. The rightmost column has text boxes labeled tag1, tag2, tag3, etc..

I have the TAB sequence set to go from color1 to tag1, color2 to tag2, color3to tag3, etc..

A user can inadvertently tab over the color1 (or color2, etc. columns) into the tag1 (or tag 2, etc. columns) column without entering data in the color column.

Is there coding to allow a TAB key entry to be a Change Event such that, if a user TABS out of color1 without entering data, a MsgBox could signal that they must enter data in the color1 field before they can continue?

Alternately, can you suggest a different approach? The goal is to require an entry in the leftmost column (color1) before they can proceed to the tag1 field. Of course, they are given a "Cancel" option.

View Replies!   View Related
Worksheet_Change Event
I have two workbooks that have the following
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myColor As Integer
With Target
If Intersect(.Cells, Range("v:v")) Is Nothing Then Exit Sub
If IsEmpty(.Cells) Then r.Offset(, 1).Interior.ColorIndex = xlNone: Exit Sub
If Not IsDate(.Cells) Then r.Offset(, 1).Interior.ColorIndex = xlNone: Exit Sub
Select Case Month(.Value)
Case 1: myColor = 3
Case 2: myColor = 17
Case 3: myColor = 19
Case 4: myColor = 22
Case 5: myColor = 26
Case 6: myColor = 33
Case 7: myColor = 36
Case 8: myColor = 38
Case 9: myColor = 40
Case 10: myColor = 42
Case 11: myColor = 44
Case 12: myColor = 7

I have this code in 1 sheet in one of the books (and all other sheets work fine), and the same code in all sheets in the other book. Both books work the way it's supposed to.

My question,
Is it necessary to have the Worksheet_Change event in all the sheets (all sheets act on the code the same way) or is it okay for just one sheet?

Could I encounter a problem if in only one sheet?

I just don't see why I would have to add more size with the code in all sheets if it is not necessary.

View Replies!   View Related
Is There A Worksheet_Open Event?
There is a Workbook_Open event in VBA. Is there a Worksheet_Open event? I.e. I want to write some code that is applied when a worksheet becomes active/is displayed to the user.

E.g. I am in Sheet1. I click a button which links me to Sheet2, however in doing that I want the value of the Active Cell in Sheet1 to be displayed in A1 of Sheet2.

Is this possible? Is there another way to do this.

View Replies!   View Related
Conditions On VBA Event
Working in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my

View Replies!   View Related
Checkbox Event
I need to have a check box, that when it is checked the user must fill in a cell.

View Replies!   View Related
Event Procedure
I've created a macro with a custom dialog box, but I don't know how to make the transition from when I make the dialog box pop up, the user enters the information, then they click "Continue" or "Cancel" or whatever it may be, how to do I make it happen from there out?

Do I make the command buttons a boolean and if they click it's true? How do I make it work?

View Replies!   View Related
OnZoom Event
I want to be able to change the size of markers on a chart as the user zooms in and out. I know that I can trap the mouse buttons and check for the zoom that way, but is there an 'OnZoom' event that I can trap in case the user uses the toolbar or menu to zoom?

View Replies!   View Related
Calendar Event Notifications
I have a calendar created as an excel workbook that currently has no functionality other than writing notes for specific dates. How can I get excel to show a message/popup/notification when the calender is opened up each time for what has been input as a note for the date today? The calendar has a year to view on one worksheet and then a month to view on subsequent tabs. please note i am a relative novice with excel.

View Replies!   View Related
Worksheet_Change Event Not Firing ...
I wrote a simple script to show/hide certain rows based on the value of a certain cell on my worksheet (cell value chosen by drop down). When I left work last night, everything worked fine. When I returned this morning, the change event no longer appears to be firing. I'm quite certain no one else accessed the file to change the coding, so my only guesses are 1)perhaps some sort of system update was applied in the middle of the night and it messed with something or 2) aliens have blocked our technology in advance of their invasion.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Address = "F1" Then

Application.EnableEvents = False

Select Case Target
Case "150"
Rows("13").EntireRow.Hidden = False
Rows("14:19").EntireRow.Hidden = True
Case "330"
Rows("14").EntireRow.Hidden = False
Rows("13").EntireRow.Hidden = True
Rows("15:19").EntireRow.Hidden = True
Case "340"
Rows("15:19").EntireRow.Hidden = False
Rows("13:14").EntireRow.Hidden = True
Case Else
Rows("13:19").EntireRow.Hidden = True
End Select

Application.EnableEvents = True

End If
End Sub

View Replies!   View Related
Change Event Not Working
Cells in Column M have a data validation drop-down list. If the user selects 'Closed', I want this code to run:

View Replies!   View Related
Issue With Textbox_exit Event
I've a userform with a textbox that allows me to pop in a comment.

I save what i write in the textbox back to my data sheet by using:

Private Sub TextBox35_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If blnHaltChange Then Exit Sub
If IsError(Application.Match(Val(TextBox1.Text), Worksheets("Project").Range("A:A"), 0)) Then Exit Sub
Sheets("Project").Range("AM" & f.Row).Value = TextBox35.Value
End Sub
However, i have a combobox that lets me select different records, and when i choose a different record, this does not trigger my exit event, and my comment is lost.

I tried putting Textbox44_Exit in my combobox change code, but it didn't like that.

View Replies!   View Related
Handle The Enter Event
The code handles keydown, keypress events with no problem. Also similar change events, and probably more.

This code is at UF initialize event:

Option Explicit
Dim TextBoxes() As New Class1
Dim ComboBoxes() As New Class1

Private Sub UserForm_Initialize()
Dim TCount As Long, CCount As Long, c As Control
TCount = 0
CCount = 0
'Set TabIndexCollection = New Collection
For Each c In Me.Controls
' TabIndexCollection.Add c, CStr(c.TabIndex)
If TypeOf c Is MSForms.TextBox Then................



View Replies!   View Related
Add Code To Worksheet Event Using VBA
i need to add a DoubleClick event to about 40 workbooks.
each has 6 sheets and the code will be added to two of them.

i can cycle thru the folders/subfolders and open the correct files.
but how do i put the code into the specific sheets using a program?


View Replies!   View Related
Change Event Error
I was wondering what I can add so this will not error when I delete the contents of the target range,

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("C11:C29, E11:E29")) Is Nothing Then
If Target.Cells.CountLarge > 2 Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target)
end sub

View Replies!   View Related
VBA Code Triggered By An Event
I have 2 worksheets ('pathways', 'pathway events').

'pathways' has unique rows with a unique ID i.e. [Pathway ID] whilst 'pathway events' has the same initial column [Pathway ID] but with multiple values of the same [Pathway ID] value.

If an [Pathway ID] value is selected in 'pathways', I want it to trigger some code which will open a new worksheet and copy the multiple rows in 'pathway events' with the same [Pathway ID] value and paste them into the new worksheet.

Is this possible to do in Excel. I normally use Access and you can have triggered events.

View Replies!   View Related
Run Macro From Event On Second Page
I have a situation where I have a macro that sorts on sheet1. I would like it to run when a cell changes on sheet2 in E9:N21. Since the cells on sheet1 are linked to data on sheet2, I cannot trigger the macro from sheet1 due to the cells being formulas instead of values.

Here is what I have tried and it is not working. I must be doing something wrong with the syntax.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(Sheets("Games")("E9:N21"))) Is Nothing Then
Exit Sub
Else
ActiveWindow.SmallScroll Down:=33
Rows("6:55").Select
Range("A55").Activate
Selection.Sort Key1:=Range("A55"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select
End If
End Sub

View Replies!   View Related
Hide/Unhide Row Event
It seems there may be an event or something that happens when a row is hidden/unhidden.
I have this simple function that gives TRUE / FALSE if the Cell's Row or Column is Hidden.

Public Function IsVisible(MyRange As Range)
IsVisible = ((Rows(MyRange.Row).Hidden = False) And _
(Columns(MyRange.Column).Hidden = False))
End Function
And a formula in a cell
=IsVisible(A1)

When I hide/unhide the ROW - the formula recalculates automatically without having to do the F2 Enter thing..

But when I hide/unhide the COLUMN, it does not recalculate automatically. I have to do the F2 Enter thing to get it to recalculate.

Even putting Application.Volatile in there doesn't do it.

I'm just curious why it recalculates when the row is hidden/unhidden but not when the column is hidden/unhidden? Is there a hidden event that could be used? I often see posts of people trying to prevent hiding rows.

View Replies!   View Related
Workbook Before Close Event
I have a sheet that on open looks at the username and determines which tabs can be seen by that user. If macro's are not enabled, I want the sheet to just display sheet 4.

here is the code I have on open that works fine: -

Private Sub Workbook_Open()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub

I have a Workbook_BeforeClose() function that does not, and I am stuck as to why!

Here it is: -

Private Sub Workbook_BeforeClose()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
ThisWorkbook.Save
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub

So basically after "Bob" is done it will save the sheet with only tab 4 visable. This means that if someone without Macros enabled opens the sheet they can only view tab 4 (I know it isn't password protected in this example, but it will be)

View Replies!   View Related
Event To Shade Specified Cells
I am trying to create an event that would shade certain cells if a cell is active.

For example if the active cell is cell J39, shade the following rows as follows:

View Replies!   View Related
Module Select Event
Is there any code that will trigger an event macro when a Module is activated in the VBE?

View Replies!   View Related
Combobox Change Event
Dim bfr As Long
bfr = ComboBox1.Value
ComboBox1.Value = bfr: Exit Sub

how come this gives me a "cant change property" error!?

The code is inside my combobox change event...

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved