excel vba - selection.clearcontents and how to reverse

[chief]

I am trying to devise a code that will allow me to show only particula
columns in a spreadsheet, or that will hide particular columns in
spreadsheet. I have set up a password form and i have a question.
When I run this and the person puts the name in as "john" and th
selection clears its contents, what code can i use so that when someon
enters the name "joe" then the values that were in those cells wil
return to their original selves. I guess sort of like a temporar
hiding of the values?

Private Sub CommandButton1_Click()
If TextBox1.Text = "john" Then
UserForm1.Hide
Sheet1.Range("C3113").Select
Selection.ClearContent

 

[Jim Rech]

When one of the "Frank"'s doesn't answer your question in 10 minutes it's
most likely because it's not clear. This isn't clear. I'm going to venture
a guess. If the user does not enter "john" in the text box and you want to
restore default values, I think your best bet may be to copy them in from
some out-of-sight holding area.

Btw, there is no reason to select a range to clear it. This is all you
need:

Sheet1.Range("C3113").ClearContents

--
Jim Rech
Excel MVP

|I am trying to devise a code that will allow me to show only particular
| columns in a spreadsheet, or that will hide particular columns in a
| spreadsheet. I have set up a password form and i have a question.
| When I run this and the person puts the name in as "john" and the
| selection clears its contents, what code can i use so that when someone
| enters the name "joe" then the values that were in those cells will
| return to their original selves. I guess sort of like a temporary
| hiding of the values?
|
| Private Sub CommandButton1_Click()
| If TextBox1.Text = "john" Then
| UserForm1.Hide
| Sheet1.Range("C3113").Select
| Selection.ClearContents
|
|
| ---
| Message posted
|

 

[Bob Phillips]

Use a hidden sheet and save them off there

Private Sub CommandButton1_Click()
If TextBox1.Text = "john" Then
UserForm1.Hide
with
Sheet1.Range("C3113").
.copy destination:=hidden.Range("C3113")
.ClearContents
End With
ElseIf TextBox1.Text = "joe"Then
hidden.Range("C3113")..copy _
destination:=Sheet1.Range("C3113")
'etc.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

AFAIK there is only one Frank<vbg>

Bob

 

[Jim Rech]

AFAIK there is only one Frank<vbg>

Hmmm. Did you know that Kabel is German for Cabal? Look it up.<g>

 

[Frank Kabel]

Hi Jim, Bob
lol

Just to give the correct translation for my name <vbg>
Kabel = cable
nothing to do with to cabal = to intrigue

 

[Jim Rech]

Per the Encarta dictionary:

ca·bal [k? bál] noun (plural ca·bals)
1. group of plotters: a group of conspirators or plotters, particularly one
formed for political purposes
2. secret plot: a secret plot or conspiracy, especially a political one
3. clique: an exclusive group of people

Of course I know no German (well, I can count to 3) but if Kabel doesn't
translate to Cabal, it should!

--
Jim Rech
Excel MVP
| Hi Jim, Bob
| lol
|
| Just to give the correct translation for my name <vbg>
| Kabel = cable
| nothing to do with to cabal = to intrigue
|
|
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
|
| Jim Rech wrote:
| >>> AFAIK there is only one Frank<vbg>
| >
| > Hmmm. Did you know that Kabel is German for Cabal? Look it up.<g>

 

[Frank Kabel]

Hi Jim
rothflol

Per the Encarta dictionary:

ca·bal [k? bál] noun (plural ca·bals)
1. group of plotters: a group of conspirators or plotters,
particularly one formed for political purposes
2. secret plot: a secret plot or conspiracy, especially a political
one
3. clique: an exclusive group of people

Of course I know no German (well, I can count to 3) but if Kabel
doesn't translate to Cabal, it should!

Hi Jim, Bob
lol

Just to give the correct translation for my name <vbg>
Kabel = cable
nothing to do with to cabal = to intrigue

 

[Bob Phillips]

Sorry Jim, that is far too subtle for me!

Bob

Hmmm. Did you know that Kabel is German for Cabal? Look it up.<g>