Excel/coding

Advertisement


Question
QUESTION: Hi Gulshan

I am hoping you can assist me ....I have these two codes and want to bring them both together on one sheet and have it function, if I place them on individual pages then they both work fine, but when I have them on the same sheet it isn't working...I am sure its something simple, please

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cancel = True


If Target.Count > 1 Then Exit Sub

Select Case Target.Address(0, 0)

 
Case "C9"

   Application.ScreenUpdating = False
 
   Sheets("Customer").Visible = True
   Range("E5").Select

Application.Run "Clearcheckbox4"


On Error Resume Next
   ActiveSheet.Protect Password:=Sheet271.Range("W1")

   Worksheets("cliententry1").Visible = xlSheetVeryHidden
   
  
   Application.ScreenUpdating = True


End Select


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, MyStr As String, Ans As String

If Not Intersect(Target, Range("C2")) Is Nothing Then
   For Each cell In Intersect(Target, Range("C2"))
       If Len(cell.Value) > 9 Then
         MyStr = cell
         Do
         Ans = Application.InputBox("You've exceed the characters allowed, Omit dashes", _
         "Shorten this description", MyStr, Type:=2)
         If Ans = "False" Then
         'Loop will restart
         ElseIf Len(Ans) <= 9 Then
         cell = Ans
         Exit Do
         Else
         MyStr = Ans
         End If
         Loop
       End If
   Next cell
End If


End Sub

thanks

Tony

ANSWER: Hi Tony,

I'm not sure what result you are trying to achieve by combining the 2 codes. Can you explain to me what is it you are looking for and maybe I can give a better answer.

Gulshan.

---------- FOLLOW-UP ----------

QUESTION: sorry this is the code I need help with



Option Private Module
Sub Auto_Open()
Application.ScreenUpdating = False

   Dim exdate As Date
   exdate = "2/1/2020"
   If Date > exdate Then
   Msg = "   Expired" & vbCr & vbCr & "  To Purchase Contact" & vbCr & "  "
   MsgBox Prompt:=Msg
       Application.Quit
       Call KillMe
   End If
   MsgBox ("Expires in " & exdate - Date & " Days " & vbCr & vbCr & "To Purchase Contact" & vbCr & "")

Application.ScreenUpdating = True
End Sub
Sub KillMe()

Application.ScreenUpdating = False
With ThisWorkbook
.Saved = True
.ChangeFileAccess Mode:=xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True

End Sub
this works great except that I wanted to have it take the date from a specific named cell sheet0 "m13" instead of me inputting the date.. so instead of always going to the vba and unprotect all I have to do is change the date In M13 and that will change the days left before it expires....

either name a cell and have it refer to that named cell or the cell itself...the worksheet is sheet0

ANSWER: That sounds easy Tony. All you need to do is replace your code with this data:

your old code: exdate = "2/1/2020"

Assuming your date is stored in cell A1,

Replace it with this: exdate = worksheets("Sheet1").cells(1,1).value

Hope this helps,
Gulshan.


---------- FOLLOW-UP ----------

QUESTION: Hey thanks

it works great......

is there a way to disable the shift key if someone tried to bypass the expiration date...

i have tried it a few times and every time i hold the shift down and click open on the file it doesn't reflect the exp date and just opens the workbook... is there a way around that

Answer
Hi Tony,

I don't think I know a way around it. Best is to put a note in the excel saying not to use the shift key.

Alternatively, you can have conditional formatting or data validation setup on your sheet to catch invalid / blank date entries.

Hope this helps,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.