Posts by shaundas

This thread has been solved. Thanks everyone.

Issue has been solved. Thanks you RoyUK.

Private Sub Worksheet_Change(ByVal Target As Range)
 
 If Target.Cells.Count > 1 Then Exit Sub

 If Not Intersect(Target, Range("A1")) Is Nothing Then
 Select Case Target.Value
 Case Is = ""
 Target.Value = "Name"
 Target.Font.ColorIndex = 24
 Case Else
 Target.Font.ColorIndex = 1
 End Select
 End If
 
 If Target.Cells.Count > 1 Then Exit Sub

 If Not Intersect(Target, Range("A2")) Is Nothing Then
 Select Case Target.Value
 Case Is = ""
 Target.Value = "Address"
 Target.Font.ColorIndex = 24
 Case Else
 Target.Font.ColorIndex = 1
 End Select
 End If
 
 If Target.Cells.Count > 1 Then Exit Sub

 If Not Intersect(Target, Range("A3")) Is Nothing Then
 Select Case Target.Value
 Case Is = ""
 Target.Value = "Suburb"
 Target.Font.ColorIndex = 24
 Case Else
 Target.Font.ColorIndex = 1
 End Select
 End If
 
 If Target.Cells.Count > 1 Then Exit Sub

 If Not Intersect(Target, Range("A4")) Is Nothing Then
 Select Case Target.Value
 Case Is = ""
 Target.Value = "Phone #"
 Target.Font.ColorIndex = 24
 Case Else
 Target.Font.ColorIndex = 1
 End Select
 End If
 
 If Target.Cells.Count > 1 Then Exit Sub

 If Not Intersect(Target, Range("A5")) Is Nothing Then
 Select Case Target.Value
 Case Is = ""
 Target.Value = "Email"
  Target.Font.ColorIndex = 24
 Case Else
 Target.Font.ColorIndex = 1
 End Select
 End If
 
 End Sub

Sorry I am new to coding not able to figure out how to counter check.

If you want to keep order number length sorter you can remove "Order" text and order start serial number to 1, also you can reduce date format say yymm follow with serial number. it will be (20031) 2nd order will be (20032) it's less possibilities to get duplicate.

Currently this method I am using for my quotation and invoicing.

Insert following excell formula in (example) cell A1 and insert order start number example (100) in cell P1

="Order"&TEXT(TODAY(),"yymmdd")&P1

Insert following vba code in sheet module, change cell address as per your location (example P1)

You can change word "Oder" to anything else you want.

The code will generate order number using current date and pretext text at the beginning "Order" and number end the end with auto increment, your order worksheet must be save to get auto incremental number at the end.

Private Sub workbook_open()
Range("P1").Value = Range("P1").Value + 1
End Sub

Hi Roy

Thank you again for staying with me. I have attached example workbook.

Regards

exampl workbook.xlsm

Hi Roy, Thank you for your respond and help.

I tried but it does not work I intended. I want cell A8 = Customer name, cell A9 = Address, cell A10 = Suburb, cell A11 = City, Cell A12 = Phone number.

I tried, If Not Intersect(Target, Range("A8:A12")) Is nothing then and declare Target.Value = "Customer name" and Target.Value = "address" and so on.

Regards

Hi RoyUk. Sorry my Apology . single cell working code is below, need modified code to work with multiple cell. Help will be great. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Cells.Count > 1 Then Exit Sub
 
 If Not Intersect(Target, Range("A8")) Is Nothing Then
 Select Case Target.Value
 Case Is = ""
 Target.Value = "Customer name"

 Target.Font.ColorIndex = 24
 Case Else
 Target.Font.ColorIndex = 1
 End Select
 End If
 
 End Sub

Hi AAE

Your code work great for me but I need the code to be working in several cells (like A1 Name, A2 address1, A3 Address2, A4 Phone number).

Help will be grateful.

Thanks

Hi All, Good morning.

I am using following vba code for above subject from RK, its work great for me, but I need the code to be work in several cell Likewise (A1, G5, C10 etc) currently work only one cell.

Help will be great. Thanks

[Put Grayed Text Into Input Cells for Instructions which Reappears if Input is Deleted

Sub workbook_open()
Sheets("Sheet1").Range("A1").Value = "Type Company Name Here !!"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.ColorIndex = 15
End With
End Sub

Grayed text.txt

Hi All, I need help with following:
I have three workbook. Workbook-1 Master Data File (Picture below)). Workbook-2 dataFORM-1 and Workbook-3 dataFORM-2.
Data FORM 1 and 2 send data to Master Data File using following code. When FORM-1 send data to Master Data file its occupied cell A2 to AD2, when data from FORM-2 arrived its insert one row down that is in AE3 instead of AE2, I wants data to be inserted next available column which is AE2. How to achieve that within below code . Also I want to close Master Data File automatically when Data FORM is closed.
Thanks Shaun
[ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1209039}[/ATTACH]

Following code using in Data FORM-2 also same code use in Data FORM-1.

Private Sub CommandButton1_Click()


Dim quotationNo As String
Dim myData As Workbook


‘Worksheets(“Sales Invoice 1”).Select
quotationNo = Range(“K6”)
‘Worksheets(“Sales Invoice 1”).Select – this line may not be necessary


Set myData = Workbooks.Open(“C:\EXcel\CustData.xlsm”)
Worksheets(“sheet2”).Select
Worksheets(“sheet2”).Range(“A1”).Select
RowCount = Worksheets(“sheet2”).Range(“A1”).CurrentRegion.Rows.Count
With Worksheets(“Sheet2”).Range(“A1”)
.Offset(RowCount, 30 ) = quotationNo
End With
myData.Save


End Sub

[ATTACH=JSON]{"alt":"Click image for larger version Name:\texcel datafile snap.JPG Views:\t3 Size:\t39.9 KB ID:\t1208940","data-align":"none","data-attachmentid":"1208940","data-size":"full"}[/ATTACH]

I have three workbook. Workbook-1 Master Data File (Picture above). Workbook-2 FORM-1 and Workbook-3 FORM-2.
Data FORM 1 and 2 send data to Master Data File using following code. When FORM-1 send data to Master Data file its occupied cell A2 to AD2 when data from FORM-2 arrived its insert one row down that is in AE3 instead of AE2, I wants data to be inserted next available column columns which is AE2. How to achieve that within below code . Also I want to close Master Data File automatically when Data FORM closed.
Thanks Shaun

Following code using in Data FORM-2 also same code use in Data FORM-1.

Private Sub CommandButton1_Click()


Dim quotationNo As String
Dim myData As Workbook


‘Worksheets(“Sales Invoice 1”).Select
quotationNo = Range(“K6”)
‘Worksheets(“Sales Invoice 1”).Select – this line may not be necessary


Set myData = Workbooks.Open(“C:\EXcel\CustData.xlsm”)
Worksheets(“sheet2”).Select
Worksheets(“sheet2”).Range(“A1”).Select
RowCount = Worksheets(“sheet2”).Range(“A1”).CurrentRegion.Rows.Count
With Worksheets(“Sheet2”).Range(“A1”)
.Offset(RowCount, 30 ) = quotationNo
End With
myData.Save


End Sub

Hi There
I am new to VB code and this form. I need help with how to delete row count in the code.
I am using three Excel (2007) Workbook. 1: Database file, 2: Form-1 and 3: Form-2. Using following code - Form-1 transfer data to Database file and occupied Cell A2: Y2 work fine. Using same code transfer data from Form-2 to database file work fine except data goes to cell Z3 due to row count formula in the code, I want data to go to cell Z2 and not look for blank row. Will be great help. Thanks

Private Sub CommandButton1_Click()
Dim itemName As String
Dim itemPrice As Single
Dim myData As Workbook


Worksheets("sheet1").Select
product = Range("A4")
'Worksheets("sheet1").Select - this line may not be necessary
price= Range("B4")


Set myData = Workbooks.Open("F:\EXcel\CustData.xlsx")
Worksheets("sheet2").Select
Worksheets("sheet2").Range("a1").Select
RowCount = Worksheets("sheet2").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet2").Range("A1")
.Offset(RowCount, 0) = itemName
.Offset(RowCount, 1) = itemPrice
End With


myData.Save


End Sub