Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
i created a form using vba in excel, what i want to do is if the cell that is clicked on is blank i would like excel to open the form...please help
Assuming the UserForm is in the same workbook, try this:
Right click the sheet tab for a sheet, select View code and paste this code into the VBA editor. Change the name of UserForm1 to match the name of your UserForm.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = "" Then UserForm1.Show
End Sub
0 
That worked but is there a way to make it so that is comes up with a double click instead of a single click
0
Just to clarify Razor2.3's excellent suggestion, use the BeforeDoubleClick event instead of the SelectionChange event.
IOW, don't just replace the string SelectionChange with BeforeDoubleClick. I don't believe that will work.
Instead, replace the entire first line with this:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Or choose the BeforeDoubleClick event from the dropdown and insert the If Target = "" Then UserForm1.Show line into the procedure.
0
A friendly suggestion...
It's helpful to those of us trying to come up with solutions if you include all of your requirements in a single post as opposed to adding things after solutions have been posted. In some cases, the additional requirements can make earlier suggestions unusable, thereby being a waste of everyone's time.
I know that it is not always possible to know what new situations might arise based on a given solution, but the more we know up front, the easier it is to come with an all-inclusive solution.
Is there anything else you want to add to your requirements before we try to answer this latest question?
0
ok i also want to date stamp the cell after information is put in and have the clear 14 days after the date of the stamp...and thats it
0
Let me see if I understand this:
If a user double clicks a cell and it is empty, a userform should pop up. I assume this userform will populate the cell with some data.
What should happen if the user single clicks or right clicks the cell?
Once a cell contains data, it should be locked for 14 days and then unlocked, cleared and available for use.
Did I miss anything?
0
thats it...when the user single clicks or right clicks it should do nothing...and i dont think this is possible but is there a way to bypass the enable macros question
0
I did think of one more thing...it would be amazing if i could remove the tab of the worksheet...is that at all possible?
0
There is no simple way to "bypass" the enable macros question, although vulnerabilities do exist. See
http://www.microsoft.com/technet/se...
and
http://www.microsoft.com/technet/se...If there was an easy way, then anyone could attach code to a worksheet and do all sorts of bad things intentionally or by mistake.
There is, however a fairly common way to deal with the question. You set up a workbook where only an instruction sheet is visible if macros are disabled. This sheet tells users to enable macros if they want to use the workbook.
See this site for one such method:
0
I figured I'd give it a day so you could add a few more requirements.
In response 7 you said "and thats it".
In 9 you added "bypassing the macros question".
In 10 you added "removing the sheet tab".
I'm sure there's more...
0
Here are some pieces and suggestions.
I'm going to have to leave it up to you to put them all together since only you know your workbook/code is laid out and how you want it all to interact.
I'll supply the tools, you put them to use...
To turn off Sheet Tabs:
ActiveWindow.DisplayWorkbookTabs = False
To turn off Right Click:
Application.CommandBars("column").Enabled = False
Application.CommandBars("cell").Enabled = False
Application.CommandBars("row").Enabled = FalseThe follow sets of code have to be used together to get the desired results. All of the testing I've done was with text, not formulae, so you might have to modify the code to make it work for every situation.
Copy/Paste all the code below into a sheet module. Note: The ResetEvents code at the bottom is just a little sub to use in case your testing crashes some code after Events have been disabled. In order to get the Events enabled again, just run that little sub.
The only way I was able to "have nothing happen when a cell was single clicked" and "lock cells that already contain data" was to save the contents of a cell when it is selected and put it back in the cell if it gets changed. There may be a way to accomplish the same thing with sheet protections and cell locking, but I couldn't get it to work.
Public OldAdd, OldVal As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'If a single cell empty, show the UserForm
If Target = "" Then UserForm1.Show
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
'If a single cell is selected save current address and contents
If Selection.Cells.Count = 1 Then
OldVal = Target
OldAdd = Target.Address
End If
End SubPrivate Sub Worksheet_Change(ByVal Target As Range)
'If a change is made, other than by the UserForm, replace the old contents
On Error Resume Next
Application.EnableEvents = False
Range(OldAdd) = OldVal
Application.EnableEvents = True
End SubSub ResetEvents()
Application.EnableEvents = True
End Sub
0
I was really hoping for something with the protection and locks...any advice on the clearing of the cell after 14 days
0
The only advice I have is to set up your UserForm macros to record the date and location of each entry on a separate sheet or in a comment tied to the cell. Then have a macro that periodically checks the current date against the recorded date for each entry. Once an entry_date + 14 equals todays_date, clear the cell. Could be easily done with On Open macro or a SheetActivate macro.
BTW...feel free to chuck in a "Thanks for your help" every now and then. I posted a rather lengthy list of suggestions and all I got was "I was hoping for something else" and "What about my other question".
0
Locking the data is surprisingly simple. As an added bonus, the data should remain protected if macros aren't enabled, assuming you pick a good password: (Note: this does not disable the context menus, nor does it hide the sheet tabs)
Location: ThisWorkbook
Option ExplicitConst strPasswd As String = "Some Password"
Private Sub Workbook_Open()
If Not ActiveWorkbook Is ThisWorkbook Then _
Exit Sub
Dim sheet As Worksheet
Dim cell As Range
On Error Resume Next
For Each sheet In Me.Worksheets
UnlockSheet sheet
For Each cell In sheet.Range(sheet.Cells(1), sheet.Cells.SpecialCells(xlCellTypeLastCell))
If cell.Comment Is Nothing Then
If Not cell.Formula = "" Then _
cell.Clear
ElseIf CLng(CDate(cell.Comment.Text)) <= CLng(Now - 14) Then
cell.Clear
ElseIf Err.Number Then
cell.Clear
Err.Clear
End If
Next
LockSheet sheet
Next
Me.Protect strPasswd, True, True
End Sub
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Target = "" Then
Me.Unprotect strPasswd
UnlockSheet Sh
UserForm1.Show
Target.AddComment CStr(Now)
LockSheet Sh
Cancel = True
End If
End SubPublic Sub UnlockSheet(sheet As Worksheet)
sheet.Unprotect strPasswd
End SubPublic Sub LockSheet(sheet As Worksheet)
sheet.Protect strPasswd, True, True, True
End SubPublic Sub helpMe()
Dim sheet As Worksheet
For Each sheet In Me.Sheets
sheet.Unprotect strPasswd
Next
Me.Unprotect strPasswd
Application.Update = True
End SubMan, that's far too big for a "surprisingly simple" solution.
EDIT: Hey, I think this is longer than DerbyDad03's post! Do I win something?
0
ok this might help some one else...this is what i have been able to do with locks and protection to lock a cell after data is put in
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, CANCEL As Boolean)
If Target = "" Then bulletin.Show
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = (("*")) _
Then Selection.Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=False
End Sub
0
ok now the last thing i need to do is delete a cell 14 days after it is filled, it auto locks after there is data in it so the cell wont change at all
0
Have you tried Razor2.3's code? It appears to do *everything* you've asked for, including locking the cells and clearing them after 14 days.
Oh I see - you've been too busy thanking everyone for their help to test his code.
0
It didnt work for me...i tried...thank you though....all i need now though is the code to clear a cell after 14 days
0
Well, everyone here (excluding you) has already agreed the code provided works, so you have a choice of explaining what's happening, pay someone a lot of money to write your VBA for you, or use a database application.
And even if you do explain, you've provided no incentive for us to continue to care.
0
the problem i am having with that code is that it is deleting every thing in the sheet regardless of when the data was entered
0
That's by design.
When you double-click, it adds a comment to the cell that contains the date data was added. When loading the workbook, we scan each cell, looking for data. If we find something in the cell, but not the date comment, we delete the errant data.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
