Renaming and hidding a sheet after taking value from a changing cell

[2_nisia]

Hello,


I have a drop down menu (in cell E6) with the following options: empty, apple, banana, pineapple. The menu is placed in a sheet called "Start".
It is already assigned that when the option is empty number 1 is assigned, for apples is 2, for bananas is 3 and for pineapple is 4. It takes these numbers from column F


In order to understand what option is chosen I have this codeline: Choice = Range("F" + Number).Value
where number is 6 (i need to state it like this because there are more drop down menus bellow E6)


Each option is unhiding a sheet that its used by the user, in order to enter some data. The sheet is called "Fruit:" + the name of the chosen fruit (Fruit:apple, Fruit:banana, Fruitineapple)



I would like to rename the "Fruit:x" sheet and make it hidden again, if the user decides to chose another option from the drop down menu, after he has already made a choice.








Number = 6


Dim Choice As Integer
Dim i As Integer


Choice = Range("F" + Number).Value

If Choice = 1 Then 'When you choose the empty cell'

If IsEmpty(Sheets("Start").Cells(Number, 7).Value) Then 'This is when there is not an existing tab'
'do nothing
Exit Sub

ElseIf Not IsEmpty(Sheets("Start").Cells(Number, 7).Value) Then 'There is an existing tab'
Msg = "There is already a choice made. Would you like to overwrite it?"
Ans = MsgBox(Msg, vbYesNo)


If Ans = vbYes Then

If i = 2 Then
Ark11.Name = "Fruit:apple"
Sheets(Fruit:apple).Activate
ActiveWorkbook.Sheets(Fruit:apple).Visible = False 'Makes hidden sheet hidden

End If

If i = 3 Then
.
.
.
End If

If i = 4 Then
.
.
.
End If

Sheets("Start").Activate 'Return to front page
Sheets("Start").Range("G" + Number).ClearContents 'Deletes the name given for "anvendelse 1"
Exit Sub



ElseIf Ans = vbNo Then
Exit Sub

End If

End If

End If

If Choice = 2 Then 'When you choose apple'
.
.
.
.
End If


If Choice = 3 Then 'When you choose banana'
.
.
.
.
End If



If Choice = 4 Then 'When you choose pineapple'
.
.
.
.
End If


i = Choice

End If


End Sub



My problem is that the code doesnt not understand the i I have set. So it doesnt rename of hide the sheet "Fruit:x" when I make a new choice.
I placed the i = Choice in the bottom, so it can save the previous choice, and use it in case the user tries to overwrite it.

Is there any solution to this?
Thanks in advance!

 

[HaHoBe]

Hi 2_nisia,

as far as I know the colon is a sign that cannot be used for a sheetname. So

Ark11.Name = "Fruit:apple"

should result in raising an error 1004.

You check the number from cell G6: how is that cell filled?

So only Start and the sheet with the fruit should stay visible if a fruit is chosen? These sheets are present in the workbook (so why would you need to rename them)?

Ciao,
Holger

 

[2_nisia]

Hi 2_nisia,

as far as I know the colon is a sign that cannot be used for a sheetname. So

Ark11.Name = "Fruit:apple"

should result in raising an error 1004.

You check the number from cell G6: how is that cell filled?

So only Start and the sheet with the fruit should stay visible if a fruit is chosen? These sheets are present in the workbook (so why would you need to rename them)?

Ciao,
Holger

Hey Holger,

My mistake; it shouldnt be colon. So lets say it is named instead: Fruit_apple, Fruit_banana, Fruit_pineapple

G6 is filled by asking the user for a name. So when the second option is chosen (meaning the apple, since the 1st option is blank), then a pop up message appears and the user can thus name G6 and also subsequently the sheet that will unhide e.g. red apples. So the sheet will be named "Fruit_red apples"

If the users first choice is apple, then the sheet Fruit_red apples appears. But if the user decides to changes his fruit to another fruit, or the blank cell, the Fruit_red apples sheet will still be there.
So I would like that in this case the sheet gets renamed back to Fruit_apple and it gets hidden.

That is because I have some code further down, that it will look for a hidden sheet with this exact name. So I dont want to mess up with the later code.

Hope my explanation was a bit more clear! :D

 

[HaHoBe]

Hi 2_nisia,

please explain what you want to do: use Data/Validation from cell E6 (all the time) or have DV only for one choice and work with names thereafter (that would mean that the sheets get copied and that a list of the newly named sheets must be saved anywhere). I can't make a plan dor any solid application from the information given up to now.

Ciao,
Holger

 

[HaHoBe]

Hi, 2_nisia,

G6 is filled by asking the user for a name.

Not in the code you supplied. You use a MessageBox which is used to display text, asking for information would mean to use an InputBox. And you would need to check for not allowed characters the user may have used.

The code supplied does not offer the opportunity to rename the sheet chosen in E6, any user would need to do so in the worksheet directly by typing in anything into the cell G6. So any change in that cell would need to make the original sheet visible, rename it in order to go on. And what should be done then?

Ciao,
Holger

 

[2_nisia]

Hi Holger,

Sorry once more that I am not that clear. The code is too big, so I probably didnt include all the relevant information.



What I want to do is find the number in F6, before the user tries to change the fruit he chose, then save this number as an information inside i (the information that is saved is numerical and its either 1,2,3 or 4).
So then i have saved in i the previous choice and inside choice i have the new choice of the user.

Then I want to go and reset the sheet that was created for the previous choice.
So I am including three "if then".
If i=2 then the code will go find the sheet of apple, rename it and then hide it.
If i=3 does the same for banana
If i=4 does the same for pineapple

Then, since the sheet is renamed back to the original, I want the code to hide it.

This whole code up until now was only in case the second choice of the user was the blank space.


So the code will be repeated three more times; if the second choice of the user is apple, banana or pineapple (choice = 2 or 3 or 4)


The actual code I am writting is of a different content, so i used the fruits instead to make it a bit more understandable but perhaps it didnt work so well :P

dont know if its gonna help or complicate things more., but thats how it actually looks like.
Kontorbygning was the option of apple, banana, etc.
2 is the number assigned automatically to apple or kontorbygning in this case
and office is the user defined name.


Hope its a bit more clear and thanks a lot for all the help and effort!

 

[HaHoBe]

Hi 2_nisia,

I must admit that I have made a lot of guesses on what I read in your post. Before I continue to work on this it's better to ask for a couple of things in order to get the information how your workbook looks like instead of me building a sample looking like I think your workbook may look like.

I put down the information in a workbook on OneDrive which holds the sheets mentioned and 3 additional sheets, one hoilding the information about the list which is used to fill the samples on Start, one with the information I have and where more information from my side is needed (sorry if questions already have been answered before) and the last being the text of this thread so far without code.

necessary	Sheetname	visible	Address	what	Question	What I have done	Names
no	Admin	not relevant	A1:A4	List for DV/Drop Down			DatList
no	Admin	not relevant	B1:B4	Numbers for entry DV
no	Admin	not relevant	A1:B4	Range for Vlookup			DatRef
yes	Fruit_apple	false		Data used for apple
yes	Fruit_banana	false		Data used for banana
yes	Fruit_pineapple	false		Data used for pineapple
yes	Start	true	E6	Data/Validation	What did you use? Data/Validation, Formula Combobox, ActiveX ComboBox?	I used Data/Validation from the List. This might have been a wrong guess and needs information prior to taking any further steps
yes	Start	true	E6		Please give information about what you have used for displaying E6, if you have used a List to fill or entered the entries by hand
yes	Start	true	F6	whatever assigned means	It is already assigned that… How?	Entered a formula with Vlookup for Value from E6 for the number
yes	Start	true	F6		Information about E6 should clarify how the assignment is done
yes	Start	true	E6		Would you like a change in E6 trigger the event of making the sheet visible?
yes	Start	true	E6	Making original visible	No problem as long as no renaimg has taken place
yes	Start	true	E6		At which point do you want to allow the user to rename the sheet: directly after making the original sheet visible? Or have another procedure to let the user decide on that later on?
yes	Start	true	E6	Making another choice	So there will always be just one sheet which name will be changed?
yes	Start	true	E6		What about the changes/choices the user has done to the renamed sheet? Renaming and hiding the sheet will not reset the sheet to it's original status - keep the changes?

Ciao,
Holger

 

[HaHoBe]

Hi 2_nisia,

I hope you're well. As I heard nothing new to this thread I'd like to ask if you found an alternative way to solve the problem?

Ciao,
Holger

 

[2_nisia]

Dear Holger,

Thank you for reaching out. I realised my issue is very complicated to describe and find a solution from here, so I am trying a few things alone.
But thanks for all the help!!

 

[HaHoBe]

Hi 2_nisia,

it's always been my opinion that the best solutions are those that anybody has created by himself. They may not ever be the most elegant ones but the person has found a way to work. And that will definitely be a way which may be remembered for any other problem as well.

And maybe it's not you who has trouble describing your problem - it could be me as well understanding what you are up to. At least I have to translate everything into my native tongue in order to understand, figure out, maybe find a solution and translate back to english leaving a lot of room open for misunderstandings.

Thanks for the update, I wish you good luck with your project.

Holger