Separate macros for each cell.

[kev]

Hi all,

Ok, this is kinda difficult to explain but i will try to. I have a
workbook with 7 sheets inside. Each sheet accounts for one big category
for example Air, Noise, Pollution. Take the "Noise" active sheet for
example, it has a Row named Procedure/Training in which i want to
enable users to input info. This info is DocumentType and DocumentName.
For this purpose, i have created a user form with combo box for
DocumentType and text box for DocumentName plus "Ok","Cancel" and
"Clear Form" command buttons. The macro works fine except for it only
returns the Document Name not the Document Type. I was expecting an
input like "Document Type": "DocumentName".

Here is the coding for the user form initialize
Private Sub UserForm_Initialize()
With cboDocument
.AddItem "Corporate Guideline"
.AddItem "Site Specific Guideline"
.AddItem "Training Course"
.AddItem "MDS"
.AddItem "Vspec"
End With
cboDocument.Value = ""
txtName.Value = ""
cboDocument.SetFocus

End Sub

The coding for Ok button is as below:

Private Sub cmdOk_Click()
ActiveWorkbook.Sheets("SERI_EQA1").Activate
Range("G3").Select
ActiveCell.Value = cboDocument.Value
ActiveCell.Value = txtName.Value
Range("G3").Select

End Sub

2) Now moving to next problem, assuming users can input the
DocumentType and DocumentName, how can i make it loop? I mean in just
one cell users might need to input in more than 1 document type n
document name. How do i achieve this?


3) I have created a button in that cell that when the users click on
it, it displays the user form. The problem is do i need to create
separate buttons for each cell. This is gonna be very tedious as there
are way too many cells (more than 50) and if i were to code for each n
every button i cannot complete the project by next year. Please help.


I know thats a lot of request in here but someone pls try to help...i
need this project completed by end of this year and this portion i
mentioned here is just one there are many more sections to be
completed.


Many thanks in advance.

Kev.

 

[Jim Cone]

Re the first question...
Private Sub cmdOk_Click()
ActiveWorkbook.Sheets("SERI_EQA1").Activate
Range("G3").Value = cboDocument.Value
Range("G4").Value = txtName.Value
Range("G3").Select
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"kev" <[email protected]>
wrote in message
Hi all,
Ok, this is kinda difficult to explain but i will try to. I have a
workbook with 7 sheets inside. Each sheet accounts for one big category
for example Air, Noise, Pollution. Take the "Noise" active sheet for
example, it has a Row named Procedure/Training in which i want to
enable users to input info. This info is DocumentType and DocumentName.
For this purpose, i have created a user form with combo box for
DocumentType and text box for DocumentName plus "Ok","Cancel" and
"Clear Form" command buttons. The macro works fine except for it only
returns the Document Name not the Document Type. I was expecting an
input like "Document Type": "DocumentName".

Here is the coding for the user form initialize
Private Sub UserForm_Initialize()
With cboDocument
.AddItem "Corporate Guideline"
.AddItem "Site Specific Guideline"
.AddItem "Training Course"
.AddItem "MDS"
.AddItem "Vspec"
End With
cboDocument.Value = ""
txtName.Value = ""
cboDocument.SetFocus

End Sub

The coding for Ok button is as below:

Private Sub cmdOk_Click()
ActiveWorkbook.Sheets("SERI_EQA1").Activate
Range("G3").Select
ActiveCell.Value = cboDocument.Value
ActiveCell.Value = txtName.Value
Range("G3").Select

End Sub
-snip-
Kev.

 

[kev]

Hi Jim,

The code you provided is not the one i am looking for as i output
document type in cell G3 and document name in cell G4. I need both to
appear on the same cell G3 like this "DocumentType: DocumentName"

Pls help.

Is anyone out there able to help me out with the rest of the questions?
please do kindly help as i am running out of time.

Thanks!

 

[Jim Cone]

Private Sub cmdOk_Click()
ActiveWorkbook.Sheets("SERI_EQA1").Activate
Range("G3").Value = cboDocument.Value & ": " & txtName.Value
Range("G3").Select
End Sub
'--------------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"kev" <[email protected]>
wrote in message
Hi Jim,
The code you provided is not the one i am looking for as i output
document type in cell G3 and document name in cell G4. I need both to
appear on the same cell G3 like this "DocumentType: DocumentName"
Pls help.

Is anyone out there able to help me out with the rest of the questions?
please do kindly help as i am running out of time.

Thanks!