Inputs on one sheet dependent on which sheet is visible.

[Shane]

I have created a workbook with a group of sheets that are agendas for client
meetings. I want to create one worksheet that is a synopsis agenda that is
prefilled based on the inputs of whichever agenda is selected.

For example: If a client is coming in for a investment planning meeting,
the advisor agenda will have different inputs than a tax planning meeting,
but I want the client agenda to show the advisor agenda section based on
which meeting it is. The agenda section of the advisor has inputs from a
userform and also has text in cells and form comboboxes. Is it possible?

I am new to coding, this is my first major project, so any help would be
appreciated!

 

[paul.robinson]

Hi
First thing to learn is how to ask the question!
Nobody really knows what you are talking about except you. Say these
words before any post!
Nobody knows what an "agenda" is or a "synopsis agenda" or an "agenda
section" - they can guess, but they don't know. Try to ask questions
in terms of what people will see on an excel sheet, exactly what the
inputs to the sheet are and where, exactly what the results of
calculations should be and so on. Try not to ask "meta" questions
about whether some idea in your head might be doable, but concrete
things like "I have 3 sheets and each has a range of data in columns 1
to 8, the first row in each range is headings and I want to copy the
first four lines of data to a single new sheet with the same headings
row and..."
Asking vague questions will put people off answering, especially the
people who can give you the best help. From experience, they know that
any answer they give won't be quite what you want, so you will come
back with further questions as your ideas are clarified for you, and
the person doing the work for you has to go back to the start again.
They don't want to waste time like that so will wait for accurate
questions.

The next step is to look at what you have so far. Is the structure of
each excel sheet what you want? How much of what is on a sheet is
static (inputted once like headings and formatting) and how much is
variable. How is new information to be added - manually, via a form,
via a formula, via a link or database upload...How will information be
combined (if at all) or refined (if at all) - do I need to create a
new sheet with combined or filtered information on it. How do I want
to create that sheet and so on and so on...

Now you can start asking questions! For each small job you don't know
how to do, ask a question e.g. If you have data in columns 1 to 8
starting at row 1 with headings in row 1, how can I filter
automatically based on the word "Yes" in column 1 and put the filtered
data on a new sheet called "Yes Data" with the same headings starting
in row 1 of this new sheet". This kind of question will usually get
you several responses within half an hour.

Browse this newsgroup for questions before posting - most things have
been answered before! Get some decent books:
Excel VBA for Dummies by John Walkenbach is a good start
Excel 2000 (2003, 2007..) VBA by John Green et al published by Wrox
Press is a good middle and end.

So far, the answer to your question is yes.
Happy coding!
regards
Paul

 

[Shane]

Thank you for your response... hopefully this is a more specific question.

My workbook has a userform that, based on the selection in one combobox in
the userform, makes one of 8 sheets visible. On each of these sheets, I have
a range of merged cells "C18:C30" which is a combination of static input and
embedded activeX comboboxes. Upon clicking a commandbutton placed on the now
visible sheet, I would like the values in this range copied to a separate
sheet, with named ranges of "CAopt1" to CAopt13".

I have created a commandbutton with the code below, but am getting a "Method
or Data Member not found" error. In the properties window for the embedded
comboboxes, it names them as I have below. What am I missing?

Sub Button163_Click()
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("S-GT Meeting")
Sheets("Client Agenda").Visible = True
With Sheets("Client Agenda")
.Range("CAopt1").Value = WS.Range("c18").Value
.Range("CAopt2").Value = WS.Range("c19").Value
.Range("CAopt3").Value = WS.Range("c20").Value
.Range("CAopt4").Value = WS.Range("c21").Value
.Range("CAopt5").Value = WS.Range("c22").Value
.Range("CAopt6").Value = WS.Range("c23").Value
.Range("CAopt7").Value = WS.Range("c24").Value
.Range("CAopt8").Value = WS.ComboBox9.Value
.Range("CAopt9").Value = WS.ComboBox10.Value
.Range("CAopt10").Value = WS.ComboBox11.Value
.Range("CAopt11").Value = WS.ComboBox12.Value
.Range("CAopt12").Value = WS.ComboBox13.Value
.Range("CAopt13").Value = WS.Range("C30").Value
End With
Sheets("S-GT Meeting").Select
End Sub

 

[Shane]

Thank you for your help. I figured a way around it.

Shane