Stop Users From Selecting Another Cell Until Current Selection Receives Data
Jul 21, 2009
Amongst the several sheets contained in my workbook, there is one called 'Inspection Report'. Users fill in whatever data is required in the other sheets, and once they get to this one, they are supposed to enter a number from 1 to 3 into Cell X1 (which is currently selected) before they select anything else. Unfortunately, I am currently unable to stop them from doing what they should not be doing.
So, I would like to have a notification of some sort pop up into their face if they click or move the selection anywhere else while Cell X1 is still empty. Something like a validation would be nice.
View 6 Replies
ADVERTISEMENT
Feb 4, 2008
is it possible to restrict a cell in such a way that it is impossible to type data into it, but rather select data from a drop down, through validation?
View 9 Replies
View Related
Feb 14, 2008
I have a sheet with 3 columns and I would like to have these 3 columns fit the screen so that an attractive user interface is created. I do not want to be able to scroll to blank area to the right of column C
I know nothing about Visual Basic and was trying to follow the following
Private Sub Worksheet_ Active ()
Me. ScrollArea = Range (Me. Used Range, Me. UsedRange (2.2) .Address
End Sub
I am using Excel 2000 and I either kept getting error messages or after changing the visual basic sheet headings to worksheet and Active respectively ( two headers at top of page) the macro still failed to work.
Colo HTML does not seen to work in the visual basic window so I cannot show you the page.
View 9 Replies
View Related
Apr 3, 2009
I need my worksheet to automatically fill in a range of cells (from Range B*:G*) with my yellow color once the last cell in the range (G*) receives data, and automatically switch back to no color if the data in Cell G* is erased. However, if somebody merges a range, it should appear as it would by default - as if no macro exists, if that makes sense. I've attached a sample worksheet.
View 2 Replies
View Related
Oct 31, 2006
I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.
Excel 2003
View 9 Replies
View Related
Nov 2, 2009
I seem to be lost in some macro-coding... I have this folder with pictures, and I want to display them as comments, by a macro. So far I have control. But then, if there is a picture that is missing, or if there is a cell that is empty in the selection, everything seems to stop.
the macro I use is very simple, but when everything is in place, its working:
Sub pics()
For i = 3 To 50
PicName = "C:UsersEspenPicturesHus" & Cells(i, 1) & ".jpg"
With Cells(i, 1).AddComment
.Shape.Fill.UserPicture PicName
.Shape.Height = 300
.Shape.Width = 400
End With
Next i
End Sub
Is it possible to make the macro run even if there are wholes, or if some pics are not store in the folder yet?
Is it possible to make the area "unlimited", as you see its now limited from 3 To 50.
And is it possible to make this run automatically, kind of checking the folder on its own to see if there is new pics?
View 9 Replies
View Related
May 7, 2006
I'm trying to create a spreadsheet that will automate our job bidding process. We have alot of standardized phrases we use and I thought it would be nice to just click on the phrases that are pertinent to the user's current bid. How do I get the user's current selection (from a form, not a cell) to append itself to the "current" end of the bid (a worksheet)? In other words, previously entered information by the user (name, address, type of bid, etc.) is already updated on the new worksheet created by the new bid, but how do I get the user's current selections to find the end of what is already there, and add itself to the bid at that point?
View 9 Replies
View Related
Apr 10, 2013
I have 5 cities and each city has a set of data in separate tabs. Right now i have data pulled for all 5 cities in a single sheet but its too cramped up..
I want to use a drop down list (data validation) and if any of the 5 cities are selected in the drop down, the data from that particular city (tab) should be pulled up. This way my sheet size will be reduced 5 time which will look good.
A common v lookup wont work because the data has to be pulled from 5 different tabs for 5 different cities.
View 2 Replies
View Related
Oct 12, 2007
I've set up code where when a cell within a specified range of cells is selected, a macro will run. This works all well and good except for when a whole row, column or range containing the defined cells is selected, there is a run time error. There is no situation where I want multiple cells selected to run the macro, so I only want to run the macro when only a single cell within that range is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("GoToRange")) Is Nothing Then
Application.Run "'Macro Test Current MY PFEP Metrics.xls'!PFEP_Filter"
End If
If Not Intersect(Target, Range("GoToRange2")) Is Nothing Then
Application.Run "'Macro Test Current MY PFEP Metrics.xls'!PFEP_Filter"
End If
End Sub
The ranges defined are non-contigious ranges.
View 3 Replies
View Related
Jul 16, 2008
I placed the code below in the ThisWorkbook section of a Service Request macro. The intention was to block users of saving the input they had just entered into the blank template. However, some continue to do it, despite the code. Obviously it is not as tight as I expected.
Does anyone know a better, tighter method, short of killing the offending user (which I am tempted to do, believe me!)?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LoginName As String
LoginName = Environ("username")
If LoginName "chuckdrago" Then
If SaveAsUI = True Then
Cancel = True
MsgBox "Saving this form is not allowed! Print a Copy"
Else
ThisWorkbook.Saved = True
Cancel = True
MsgBox "Saving this form is not allowed! Print a Copy"
End If
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
View 9 Replies
View Related
Jan 24, 2013
I would like to know if there is a way to expand the selection of cells from the current cell you are on to the very bottom cell available in the spreadsheet.
For example, I want to apply a formula from A12 - the very last A cell row in the spreadsheet.
View 1 Replies
View Related
Jun 2, 2006
I am currently setting up protection on an excel sheet - I have a proceedure to open a specific worsheet and lock and unlock cells according to whether or not certain information exisit. My question is, although I can lock cells and prevent users changing them using code I can't seem to stop them selecting cells in the first place - this is obviously achieveable from the 'Protection'>'protect workbook' menu but I want to do it from code since the sheet is unprotected and reprotected a number of times during a session....
View 4 Replies
View Related
Apr 4, 2014
I have a few excel files in a common shared folder. I want to allow other users to access this folder to make changes in the worksheet but should never allow them to delete the file. How can i do this?
View 5 Replies
View Related
Jul 28, 2014
The code that I'm using is supposedly not allow spreadsheet to be opened after a certain date, or does not allow a user to press any of the command buttons and comes up with a message saying the spreadsheet is closed for new entries.But is not working for me.
I placed the Code in the ThisWorkbook module.
[Code].....
View 8 Replies
View Related
Jul 23, 2014
I am looking at not allowing users to select the header and footer info. I don't want them to delete it. I have tried to protect the sheet and workbook but not working.
View 1 Replies
View Related
Aug 31, 2006
I am creating time sheet application , obviously i need to store the time when the user logs & logs out .. The issue is, the user can "Cheat" by changing the system time ...
Any alternatives?? Is it possible to store the time from a particular server etc or some other source.
View 9 Replies
View Related
Jun 20, 2013
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
View 12 Replies
View Related
Sep 18, 2008
I want to stop the user selecting an entire row by clicking on the row header.
How can I do this?
View 9 Replies
View Related
Oct 10, 2013
how do I select all cells from say B6 to the one above the active cell, and all the cells below the active cell, ie i want to select all cells in a column except the active cell and the first 5 cells. (in VBA of course).
View 8 Replies
View Related
Oct 1, 2009
I use Excel mainly for creating & updating simple logs, and the problem I keep running into is that when I click in a cell, it will automically select & highlight multiple cells below the one I am trying to work in. It will select anywhere from 4-5 cells up to 13. I don't know what this function is, and I don't know how to fix it. But it makes it very difficult to navigate and cut/paste or change a formula.
View 5 Replies
View Related
Jul 11, 2006
I have part of this routine working correctly. It will go and find the value I need and move to the next column using the Offset method, but now what am I doing wrong to try and get the code to select to the end of the current column?
Sub Find_First()
Dim FindString As String
Dim rng As Range
'FindString = InputBox("Enter a Search value")
FindString = Worksheets("Template").Range("A26").value
If Trim(FindString) <> "" Then
With Sheets("Service Level Score Paste Sheet").Range("A:A")
Set rng = .find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
rng.Select...........................
View 8 Replies
View Related
Jun 14, 2007
If nothing is typed in and the OK button is pressed, it changes the entire sheet to values. I'd like the macro to stop if nothing is selected.
Dim sCol As String
sCol = InputBox("Select Column")
Set UserRange = Range(sCol & "9:" & sCol & "35")
UserRange.Value = UserRange.Value
View 6 Replies
View Related
May 30, 2013
I currently have this beast of a code running in Excel 2013:
HTML Code:
Sub OBTAIN_RAW_DATA_MACRO()
Dim NextCol As Long
'Copy data
Workbooks.Open Filename:= _
[Code] ........
However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.
Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?
View 4 Replies
View Related
Aug 27, 2012
I have to run reports every week where I show the number of days items are pending some sort of action. I use an autofill action in a macro but the number of rows changes every week. Here is the current section of my macro that I use. I just input a row number that I know will not exceed the number of rows required by my data (12900). What can I replace this with (Selection.AutoFill Destination:=Range("J2:J12900") so it will only fill the number of rows current filled in in either row I or K?
Range("J2").Select ActiveCell.FormulaR1C1 = "=R1C19-RC[-1]" Selection.AutoFill Destination:=Range("J2:J12900") Range("J1").Select Selection.Copy Columns("J:J").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=FalseApplication.CutCopyMode = False
I have a date in cell s1 that I subtract from the date in row "I". I then copy the result of row J and paste it as values.
View 6 Replies
View Related
Jul 25, 2008
I've tried Add Name code but it's not working the way I want. It always refers to the same range like for example
RefersTo:="=sheet1!$a$1:$c$20"
View 12 Replies
View Related
Oct 31, 2008
Define Name to current selection....
View 3 Replies
View Related
Nov 22, 2006
how I can disable an InputBox? I've got some code that whenever someone selects a cell in a specified range, an input box pops up (running a macro) - this can get annoying sometimes though if just browsing. Does anyone know a macro where I can "disable" this?
View 4 Replies
View Related
Jun 5, 2008
I am trying to select the cells in a column and then all of the adjacent cells to the right.
If I record a macro, it looks like this:
Range(Selection, Selection.End(xlUp)).Select
Range("C5:D9").Select
How can I turn those static cells into volatile cells (I think that's what you call it)?
I basically want to select from the active cell (C9) to the beginning of the column (but leave out of the selection the header row) and then the adjacent cells to the right (D5:D9), but not with fixed cell references.
View 9 Replies
View Related
May 4, 2009
Hi guys i am in assistance of your brilliant brains, i have a working drop down menu which when selected i can select my data range such as below: ...
View 6 Replies
View Related
Aug 17, 2009
I've got a worksheet_selectionchange macro on a sheet, and another macro that you can run after it. The issue is that when the second macro runs, it also runs the selectionchange macro, and wipes some of the info that the second macro should be copying.
Is there a piece of code that I can use in the second macro to block the selectionchance code from running until it's compelte?
View 6 Replies
View Related
